Looping with VBA

[Alex]

Hello

I have written a Macro in VBA. I am familiar of how to
loop within a macro e.g using For...Next etc, but how do
I get the macro to repeat itself?

Specifically I have a button on my Excel spreadsheet
which I click to execute my VBA program. I need to click
this button 500 times (!) to repeat the process 500 times
(it invloves random numbers and this is why I need to
repeat the program - it is like a simulation). How can I
get my VBA program to repeat itself 500 times and save
the muscles in my index finger!

Any ideas always well recieved...

Alex

 

[Jack Schitt]

What would happen if you surrounded your macro code with a For .. Next loop
that counts to 500?
ie

Sub YourMacro()
Dim I as Integer
For I = 1 to 500
'rest of your code in here
Next I
End Sub

 

[Norman Jones]

Hi Alex.

Try something like:

Sub Tester()
Dim i As Long

For i = 1 To 500
'Your code
Next i

End Sub

 

[icestationzbra]

just a suggestion:

write two macros.

*****

sub yourMacro()

'your code goes here

end sub

*****

sub repeatMacro()

dim i as integer

for i = 1 to 500

call yourMacro

next i

end sub

*****

call repeatMacro on the click event of the button.

see if it helps

 

[icestationzbra]

... pretty duh of me, the other one is better..

 

[Jack Schitt]

You may want to put an
Application.CalculateFull
command just before the
Next I
command.

 

[Guest]

Norman

Yes. That works perfectly. Excellent help.

Thanks

Alex

 

[Guest]

Hello

Yes I used Norman's post...it was the first one I read
and it worked out for me.

Still, I appreciate your suggestion.

Thanks

Alex

 

[Guest]

Hello

Thanks for the advice. I actually used another post only
because it was the first one I read. It was almost
identical to your suggestion and yours worked fine also.

Thanks again

Alex