Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.
Let’s look at each one in turn and use them in a simple way.
Do Loop
START: Do (tell our Loop)
STOP: Do While (This is very similar to our Do Loop we just used above. The only difference is, instead of the condition we set (iMyNumber < 1000) being checked AFTER the Do has run at least once, the Do While will check the condition BEFORE it runs. Let’s say our Variable iMyNumber has already had the Value 1001 parsed to it. In the Do Loop we used above, it will NOT know the Value of iMyNumber until it has run ONCE. This would mean our Variable would come out of the Do Loop with a Value of 1002. But in the Do While below, it would never even enter the Loop)
Sub My_Do()
Do
<Code to repeat>
Loop While <Whatever>
End Sub
Progress: Add the number one to a Integer Variable we called “iMyNumber”
Sub My_Do1 ()
Dim iMyNumber as Integer
Do
iMyNumber=1+iMyNumber
Loop While <Whatever>
End Sub
Progress: Ok, so we have now told our “Do” what it is we want to do. Now we have to tell it when to stop. To achieve this we must set a condition for our “Loop While”. In this case we will: Do iMyNumber=1+iMyNumber until iMyNumber is > 1000.
Sub My_Do2 ()
Dim iMyNumber as Integer
Do
iMyNumber = 1 + iMyNumber
Loop While iMyNumber < 1000
Range(“b2”).Value = iMyNumber
End Sub
So the bottom line here is the Do Loop will Loop 1001 times.