How to work with While and For Loops in VBA?

This page will go through Looping. An essential part of writing excel macros as that is what causes the repetitive behavior in your scripts. Testing criteria, cell value's colors or texts. This, in combination with IF statements is a powerful combination and works as a great founding block to any programmer regardless of the programming language. Loops can be divided in to two types of loops: For Loops, or While Loops. For Loops are definitely the ones I use the most. Whilst creating While loops can be a good thing sometimes it can also get your code into an annoying endless circle as it is basically just a for loop to infinity (or laptop crashing).

There are also two popular versions of the For Loop: 1. For i where 'i' is a counter in number series or... 2. For Each Cell where is the 'Cell' is a part of a greater Range.

For i= 1 to 10
   'Do something.
Next i

Alternatively we can use the For each syntax below.

For Each Cell in Range("A1:A10")
   'Do something.
Next Cell. 

For Loops examples

Looping to 10 step 1

Sub Loop1 ()

'Clears any previous content in the sheet. 
Cells.Clear

'Steps 1 cell each time and prints it in column A. (1,2,3...10)
For i = 1 To 10
    Cells(i, 1) = i
 'Range("A"&i), this the same thing.
Next i

End Sub

Looping to 10 and steps 2 cells in each loop

Sub Loop2()

Cells.Clear

'Steps 2 each time and prints it in column A.(1,3,5...9)
For i = 1 To 10 Step 2
    Cells(i, 1) = i
Next i

End Sub

Reverse Looping

Sub LoopReverse()

Cells.Clear

'Steps -1 each time and prints it in column A.
'The result is exactly the same as our first loop. 
'However, this is printed in the reverse order starting from A10.

For i = 10 To 1 Step -1
 'This will print in reverse order.
    Cells(i, 1) = i
Next i

End Sub

For Each Cell Loop

Sub LoopForEach()

'We are defining a counter variable here.
i = 1

For Each Cell In Range("A1:A10")
 
  'Prints out the current "i" value. and adds +1 
   for each loop.
    Cell.Value = i
    i = i + 1
 
Next Cell

End Sub

Double Looping (a loop in a loop.. I know, Inception right!!)

Sub LoopDouble()

'Double loop
'For every loop 'i' value the inner loop 'j' will complete its loop to 10.
'Steps into the first outer loop with variable i. 
For i = 1 To 10

'Steps into the second inner loop with variable j.
    For j = 1 To 10
        Cells(i, j) = i * j

    Next j
Next i
'Exiting the inner loop 'j' first and then 'i'
​
End Sub

While Loops examples

Looping to 10 step 1 (same result as above but now with 'While')

Sub LoopWhile()

i = 1 'Need to set a value for i in order to start on cell A1.
'Steps 1 each time and prints it in column A. (1,2,3...10)

Do While i <= 10
    Cells(i, 1) = i
 
    'Don't forget this part. If you do, it will be an endless loop.
    'Of course you can add any value you wish to variable "i".
    i = i + 1
Loop

End Sub

Loop with IF statement

Sub LoopIF()

'Steps 2 each time and prints it in column A.(1,3,5...9)
For i = 1 To 10
    'prints out our number series.
    Cells(i, 1) = i
 
    'This will color all even cellnumbers to yellow.
    'Mod 2 is checking the remainder of a division by 2.
    'since even numbers are evenly divided by 2 there is no remainder.
 
    If i Mod 2 = 0 Then
  'colors blue for even numbers.
        Cells(i, 1).Interior.Color = RGB(100, 100, 255)
    End If
 
Next i

End Sub

Loop with IF ELSE statement

Sub Loop_IF_Else()
'Steps 2 each time and prints it in column A.(1,3,5...9)
For i = 1 To 10
    'prints out our number series.
    Cells(i, 1) = i
 
    'This will color all even cellnumbers to yellow.
    'Mod 2 is checking the remainder of a division by 2.
    'since even numbers are evenly divided by 2 there is no remainder.
 
    If i Mod 2 = 0 Then
 'colors blue for even numbers.
        Cells(i, 1).Interior.Color = RGB(100, 100, 255)
    Else
   'colors yellow for uneven numbers.
        Cells(i, 1).Interior.Color = RGB(255, 255, 0)
    End If
 
Next i

End Sub

Double Loop with IF, ELSEIF, ELSE statement

Sub LoopDouble_Ifs()

'Clears any previous cells
Cells.Clear

'Double loop
'Steps into

For i = 1 To 10
    For j = 1 To 10
 
  'Printing out the number
        Cells(i, j) = i * j
 
 'Let's add some colors
 
  'i*j<10
        If i * j < 10 Then
 'Red colors up to value 9.
            Cells(i, j).Interior.Color = RGB(255, Rnd * 255, 0)
 
 '10 <= i*j <= 20
        ElseIf i * j >= 10 And i * j <= 20 Then
 'Blue colors between values 10 and 20.
            Cells(i, j).Interior.Color = RGB(0, Rnd * 255, 255)
 
 'i*j = 49 or i*j = 100
        ElseIf i * j = 49 Or i * j = 100 Then
 'Green color for value 49 and 100.
            Cells(i, j).Interior.Color = RGB(150, 200, 100)
 
  'i*j = Any number except value 72.
        ElseIf Not i * j = 72 Then
 'colors the rest yellow, except values 72.
            Cells(i, j).Interior.Color = RGB(255, 255, 0)
 
        End If
 
    Next j
Next i

End Sub