4. Looping Statement

Looping statements are used to run the same block of code a specified number of times.

In Macros we have four type of looping:

• For...Next statement - runs code a specified number of times
• For Each...Next statement - runs code for each item in a collection or each element of an array
• Do...Loop statement - loops while or until a condition is true
• While...Wend statement - Do not use it - use the Do...Loop statement instead

For...Next statement

Example below, the counter (i) is INCREASED by two, each time the loop repeats until  i = 10 the loop repeats will stopped.

For i=2 To 10 Step 2
  your code
Next

Example below, the counter (i) is DECREASED by two, each time the loop repeats until  i = 10 the loop repeats will stopped.

For i=2 To 10 Step -2
  your code
Next

Loop repeats for each item in a collection, or for each element of an array.

for i = 1 to 10

     Range("A" & i).select

     If ActiveCell.Value = "" then

        Exit for

     End if

Next

For Each...Next statement 

Dim cars(2)
cars(0)="Volvo"
cars(1)="Saab"
cars(2)="BMW"

For Each x In cars

    i = i + 1
  Range("B" & i).select 

    ActiveCell.Value = x
Next

Do...Loop statement

This loop will be executed as long as i is different from 10, and as long as i is greater than 10. Try copy this code paste into your excel macros and run it you will see the result.

Do Until i=10
  i=i+1
  Range("C" & i).select

    ActiveCell.Value = i
Loop

While...Wend statement

Loop repeats until I  less than 10. Please tried it in your excel Macros. 

Dim i As Integer

While i < 10

i = i + 1

Range("D" & i).Select

ActiveCell.Value = "Column" & i

Wend