VBA For Loops: Simple Examples

A VBA For loop allows you to repeat a block of code a set number of times. In its most basic form, shown below, a variable i keeps track of how many times the loop has ran (Note: it is quite common across programming languages to use the variables i, j, and k as loop counters). You set the initial value of i and then specify what you want the ending value of i to be. In the following examples, you would replace [statements] with code that you want repeated.

VBA For Loop: Simple Example

For i = 1 to 10

    [statements]

Next i

In the example above, you set i equal to 1, and then each time the loop is ran, i will increase by 1. The last time through the loop will be when i is equal to 10.

Change the Step Value

For i = 1 to 10 Step 2

    [statements]

Next i 

In the example above, each time through the loop, i will increase by 2 rather than 1. This means the first time through, i will be 1, then 3, then 5, then 7, then 9. It will not run a sixth time because the next value (11) is greater than 10.

VBA For Loop: Stepping Down

For i = 10 to 1 Step -1
    
    [statements]

Next i

In the example above, the starting value of i will be 10. Each time through the loop the value of i decreases by one until i equals 1, which will be the last time through the loop.

Exiting Early

For i = 1 to 10
  
    If [condition] Then
        Exit For
    End If

Next i

In the example above, inside of the loop an If statement checks if a condition is true. If the condition is true, the Exit For code will exit the loop early.

VBA For Loop: Using Variables

limit = Sheet1.Range("A1").Value

For i = 1 to limit

   [statements]

Next i

In the example above, the variable limit is set to be equal to the value of Cell A1. This variable determines how many times to run the For loop. Variables can also be used to set the starting value of i, or the Step value. It just depends on what the needs are of your code.

Nesting Loops

For i = 1 to 10
    For j = 1 to 10
        
        [statements]
    
    Next j
Next i

In the example above, the code inside the loops will run 100 times. The first ten times through the loop, i will have a value of 1, and j will go from 1 to 10. On the eleventh time through the loop, i will have a value of 2, and j will reset back to 1. It will run ten times with i having a value of 2 as j goes from 1 to 10. On the twenty first time through the loop, i will have a value of 3 and j will reset back to 1. This pattern continues until the last time through when i will have a value of 10 and j will have a value of 10.