Looping w/calculation
Thanks Chip, worked great.
-----Original Message-----
My problem, begins at 'Calc Min Max, is that I am using
the value of E2 to calc the values in H2, I2 ang J2. That
works fine. Problem begins when I have to step down and
use E3 to calc the values of H3,I3 and J3 and so on down
the spreadsheet. The looping should stop when E? is
blank.I think I should be using a varible instread of the
actual cell ref. Code attached below. Excuse my rookie
coding! Thanks for the looping help.
Sub InventoryCalc()
Dim Months As Integer
Dim x As Integer
On Error GoTo Oops
Months = InputBox("Enter the number of MONTHS USAGE
importing for the as400: Select between 1 and 12
months!")
If Months 12 Then
MsgBox "Selection is out of Range...aborting!"
Exit Sub
End If
'Calc Min Max
Range("E2").Select
x = ActiveCell.Value
Range("H2").Select
ActiveCell.Select
'Max28
Range("H2").Value = ((x / (Months * 30) * 28))
'Max14
Range("I2").Value = ((x / (Months * 30)) * 14)
'Min7
Range("J2").Value = (x / (Months * 30)) * 7
Oops:
MsgBox "Numbers between 1 and 12 ONLY!"
mcrHideEF
End Sub
.
|