Looping w/calculation
 

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

Looping w/calculation
 

Here you go:


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

Do Until IsEmpty(ActiveCell)


x = ActiveCell.Value

'Max28

ActiveCell.Offset(0, 3).Value = ((x / (Months * 30) * 28))
'Max14
ActiveCell.Offset(0, 4).Value = ((x / (Months * 30)) * 14)
'Min7
ActiveCell.Offset(0, 5).Value = (x / (Months * 30)) * 7

ActiveCell.Offset(1, 0).Select


Loop

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


.