Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
looping a calculation until workbook closes | Excel Worksheet Functions | |||
Doing a calculation, then looping for several of the same calc | Excel Programming | |||
Looping | Excel Programming | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |