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
 

"Icon made by Freepik from www.flaticon.com", "Icon made by Surang from www.flaticon.com", "Icon made by Pixel perfect from www.flaticon.com", "Icon made by Phatplus from www.flaticon.com"

vba insert into sql server, excel custom ribbon vba, vba code for sending email from excel sheet, hidden vba code in excel, VBA secret tricks