Excel VBA printing query

[vbab]

In my line of work I need users to enter a consignment size, i.e. 25,800. From here, a mini program would...

1. Determine whether the size is between 0-10,000, 10,000-20,000, 20,000-30,000, 30,000-40,000, and 40,000-50,000.

2. Once the correct range is determined, it breaks down that consignment size into smaller lots based on constants within each range: 0-10,000 (constant is 3,500), 10,00-20,000 (constant is 4,500), 20,000-30,000 (constant is 6,000), 30,000-40,000 (constant is 7,000), and 40,000-50,000 (constant is 8,000). Thus, a consignment size of 25,800 breaks down into 4 lots of 6,000. Next, I would like to display, "1","2","3","4" in column 1 (C1) with the corresponding lot size next to each in column 2 (C2):
C1 C2
1 6000
2 6000
3 6000
4 6000

Each constant also has its own increment total. For a constant of 6,000 the increment total is 28. This increment total would be column 3:
C1 C2 C3
1 6000 28
2 6000 28
3 6000 28
4 6000 28

3. I would like to have the remainder, 1,800, calculated and displayed as a final lot size:
C1 C2
5 1800

As the final lot size varies with each new problem, it will be determined by a formula: remaining lot size divided by 1000, the result squared (square root), and that remainder multiplied by 15. So, for an 1800 lot size, the increment total would be 20.
C1 C2 C3
5 1800 20

4. All of these calculations would simply go on behind the scenes and appear on a formatted sheet that the user does not need to see until it is printed off. What the user wants is simply to have his or her sheets broken down and ready to print.

5. The next big problem is to enable conditional printing so the data above (including everything else on the formatted sheet in which it appears) is printed off as many times as there are lots. In this case, five sheets would print out; no more, no less. Each sheet would also indicate, in a particular cell, what lot is represented by that sheet, i.e. "Lot 1".

6. The data entry page would look something like this:

Consignment Size for A: [Enter data here]
Print Pages for all lots calculated for consignment size [Press this button]

Consignment Size for B: [Enter data here]
and so forth...

I would like to allow the user to enter 5 consignment sizes with a print button for each.

Any help on this would be greatly appreciated. Vbab

 

[macropod]

Hi vbab,

Don't mean to be picky, but the first thing you need to define correctly is the ranges. As defined (0-10,000, 10,000-20,000, 20,000-30,000, 30,000-40,000, and 40,000-50,000), they all overlap @ n0,000. So, do you want:
1-10,000, 10,001-20,000, 20,001-30,000, 30,001-40,000, and 40,001-50,000
or
0-9,999, 10,000-19,999, 20,000-29,999, 30,000-39,999, and 40,000-49,999
or maybe something else?

Cheers

 

[mikej336]

I'm not sure what your asking for... Seems like a pretty straight forward program. You have everything pretty well defined.

You could have this done in an hour or so witj Excel and VBA.

If your looking for a push in the right direction. Start with a Select Case Statement.

Your Old Uncle Mike

 

[vbab]

VBA is not my forte.
Thank-you macropod for pointing out the range limits.

If I have 5 ranges, say 0-9,999, 10,000-19,999, 20,000-29,999, 30,000-39,999, 40,000-50,000, with constants for each range, 3,500, 4,500, 6,000, 7,000, 8,000, is it possible to group these in the macro?

For example, the first in the range is 0-9,999 with a default of 3,500:

Dim myVal As Long
Const nDefault As Long = 3,500
Const nMin As Long = 0
Const nMax As Long = 9999

Application.ScreenUpdating = False
myVal = InputBox ("Input a number between 0 and 50,000")
If myVal > nMin And myVal < nMax Then
If my Val > nDefault Then
For i = 1 To myVal \ nDefault
Cells (i, "A").Value = i
Cells (i, "B").Value = nDefault
Cells (i, "C").Value = (nDefault / 1000) * (nDefault / 1000) * 15
Next i
End If
Cells (i, "A").Value = i
Cells (i, "B").Value = myVal Mod nDefault
Cells (i, "C"),Value = (Cells(i, "B").Value / 1000) * (Cells(i, "B").Value / 1000) * 15
End If
Application.ScreenUpdating = True

Ok, this macro works out the first range but does not return a value to column "C" nor does it allow for other ranges. Is there a way around this?

 

[WalterContrata]

vbab,

Good start. Now to follow Uncle Mike's advice,

Option Explicit

Sub LotCalc()

Dim myVal As Long
Dim i As Long
Dim iRemainder As Long
Dim nLot As Long
'Const nMin As Long = 0
'Const nMax As Long = 9999

   myVal = InputBox("Input a number between 0 and 50,000")
    Select Case myVal
    
    Case Is < 10000
        nLot = 3500
    Case Is < 20000
        nLot = 4500
    Case Is < 30000
        nLot = 6000
'And so on...

    Case Else
        MsgBox "Number too large!"
        Exit Sub
    End Select
    
    iRemainder = myVal
     For i = 1 To myVal / nLot
        Cells(i, "A").Value = i
        Cells(i, "B").Value = nLot
        Cells(i, "C").Value = (nLot / 1000) * (nLot / 1000) * 15
     Next i
    Cells(i, "A").Value = i
    Cells(i, "B").Value = iRemainder
    Cells(i, "C").Value = (iRemainder / 1000) * (iRemainder / 1000) * 15
         
     
   Application.ScreenUpdating = True

End Sub

Best Regards,
Walter

 

[WalterContrata]

As for printing, why not use the VBA macro recorder to make code, and then modify it?

Best Regards,
Walter

 

[mikej336]

Remember you can get your values right frow a cell

myval= Range("A5").Value

I like to put a button on the page. Have the user just enter values on the screen and hit the button. Call it GO or CALCULATE or something.

Your Old Uncle Mike