Looping in VBA - ChkFirstWhile?

[Rosey08]

Hello - Below is a section of code that I created using the Macro Recorder in Excel 2010, to perform a model calculation and then paste the results in successive rows within the model:

Sub RunSimTest()
'
' RunSimTest Macro
'
'
Range("D9").Select
ActiveCell.FormulaR1C1 = "1"
Calculate
Application.Run "RiskRibbonEvent_DeferredCommandClick"
Application.Run "RiskRefreshRibbon"
Range("O2:P2").Select
Selection.Copy
Sheets("EF Data").Select
Range("W20:X20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
End Sub


This macro performs an action that I would like to have performed repeatedly until the model reaches a stopping point. If I repeated over and over again with the Macro Recorder the only things that change in the code from iteration to iteration are noted below in red (and in the code above in red as well).

1) The "1" would increase by 1 for each loop. So the second time through it would read - ActiveCell.FormulaR1C1 = "2".
2) The paste range should be moved down one row for each loop. So the second time through it would read - Range("W21:X21").Select


Can anyone tell me of a way to change the code so that the action will repeat itself 500 times (in other words, it will stop after the red number 1 in the code is 500)?

Thank you!

 

[lenze]

Here's another approach. Notice there is no use of Select. It is seldom needed in VBA. This does assume, however, that there is a header or data in header in W19 on sheet EF Data.

Sub Test()
Dim LR As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("EF Data")
Dim x As Long
Application.ScreenUpdating = False
For x = 1 To 500
sh1.Range("D9") = x
Calculate
Application.Run "RiskRibbonEvent_DeferredCommandClick"
Application.Run "RiskRefreshRibbon"
LR = sh2.Cells(Rows.Count, "W").End(xlUp).Row
sh1.Range("O2:P2").Copy
sh2.Cells(LR + 1, "W").PasteSpecial Paste:=xlPasteValues
Calculate
Next x
Application.ScreenUpdating = True
End Sub

lenze

 

[Rosey08]

Thank you. That worked great!

Follow up question... each loop has about a 2-3 minute run time. So running 500 loops can take quite a while. If I wanted to stop the macro while it was running, what do I do?

Thanks again!

 

[lenze]

You can use the ESC key to stop the macro, but maybe you just want to specify the number of loops each time?

Dim x As Long
Dim y as Long
y = InputBox("How many loops do you require?")
Application.ScreenUpdating = False
For x = 1 To y
sh1.Range("D9") = x
Calculate
'etc