How to pause/ terminate/ restart a FOR LOOP

[nagovind]

Is there is any possibility to pause or terminate or restart a RUNNING FOR LOOP?


For e.g


For i = 1 to 100000


'codes here


Next i


When the above for loop is running outputs are changed according to variable i

and the same is updated with Application.screenupdate = True function.


However there is a need to PAUSE/ TERMINATE/ PAUSE - RESUME the RUNNING displayed values


Is there is a way to control the loop

Please advise

 

[Luke M]

Here's one way to do it.

Insert a check box in your worksheet and link it to a cell (say, A1). Code then is something like:

[pre]

Sub StopStart()

Do Until i > 100000 Or Range("A2") = False
DoEvents 'Let's the user interact with worksheet
If Range("A1") Then 'If turned on, do stuff
i = i + 1 'increment our counter

'Example of something to do
Range("B1") = i
End If
Loop
End Sub

[/pre]
In the worksheet, you can toggle the checkbox to pasue/start the code. To exit the loop early change A2 to be false (various methods you could use)

 

[nagovind]

Thanks Luke

The code is not working


I have entered/ Typed TRUE in cell A1 and FALSE in A2 then by placing the cursor in VBA after cliking the F8 it is not going after the first statement it is exiting


value is not dispalyed in B1 cell even after several F8 stroke


I even tried by i <= 100000 condition but still not working

Please advise

 

[mgesh2002]

Hi nagovind,


Follow these steps,

1. First, replace A2 with A1 in the first line of loop

like this:- Do Until i > 100000 Or Range("A1") = False

2. Insert Checkbox from Developer Tab (Developer -> Insert -> Checkbox)

3. Now, right click Checkbox -> Format Control -> Select $A$1 from Cell Link -> Ok


Now, run the Luke's code

 

[nagovind]

@ mgesh2002

Thanks for your added points

Yes it is doing the job for TERMINATING the loop at the mid of the loop

but the it is not possible to RESUME/ RESTART the loop


But i did some additions and found the solution as below for PAUSE and RESUME the loops with the help of your codes


Sub StopStart()


Do Until i > 100000 Or Range("A1") = False

DoEvents 'Let's the user interact with worksheet

If Range("A2") Then 'If turned on, do stuff (here i have changed A1 to A2 thats all)

i = i + 1 'increment our counter


'Example of something to do

Range("B1") = i

End If

Loop

End Sub


Then i have linked one more check box with the cell link to A2

Now its done


This code is used to


PASUE


RESUME


TERMINATE


the LOOP while running the lengthy loops as and when required


Hats off to Chandoo Fourms