Excel VBA For Loop / For...Next

This tutorial explains how to use For Loop (For…Next Loop) to perform iteration in Excel VBA.

What is Loop?

Loop is repeating a set of actions. When you listen to a song repeatedly, it is said you are looping the song.

In VBA, there are different kinds of loop methods, they mainly differ in the way that how the loop is triggered and ended.

What is For Loop? (For…Next Loop)

For Loop (For…Next Loop) is the most frequently used loop, using For to begin loop, Next to end loop, Exit For to exit loop

Syntax: (codes in square brackets are optional)

For variable = start_value To end_value [Step step_value]
    your statement...
    [Exit For]
Next [variable]

• - variable is a temporary name to get the loop started and to use it inside the loop statement. Usually programmer declare it as “i”, which standards for iteration, but it can be anything you like. I personally prefer to use i > j > k. For spreadsheet, I use c to stand for column, r to stand for row


• - start_value and end_value are where the variable begin and end. The difference between start_value and end_value are to indicate the number of loops to perform. For example, start_value is 1 and end_value is 3, then the code is to repeat 3 times, same for looping from 2 to 4.


• - step_value is the increment of variable after completing each loop. If you omit the Step statement, the default  step_value is 1. At the end of the first loop, start_value is increased by 1, at the end of 2nd loop, start_value increase by 1 again, the loop stops until start_value + increment > end_value


• Similarly, negative step_value  is to decrease variable. For example, when step_value = -1, start_value is decreased by 1 at the end of each loop until start_value-decrement < end_value

Example of simple For Loop

The following example fill A1 value as 1, A2 as 2, A3 as 3

Public Sub example1()
    For r = 1 To 3
        Range("A" & r).Value = r
    Next r
End Sub

Result

Example of Exit For with If Else

In the below example, “If…Else” statement is inserted inside a loop to conditionally control exiting loop

Public Sub example2()
    For r = 1 To 3
        If r = 3 Then
            Exit For
        Else
            Range("A" & r).Value = r
        End If
    Next r
End Sub

Alternatively, you can write an “If” statement without “End If” in this way

Public Sub example2()
   For r = 1 To 3
        If r = 3 Then Exit For
        Range("A" & r).Value = r
    Next r
End Sub

Result

Example of For Loop using negative Step:

Since variable is decreased by 1 at the end of each loop, therefore start_value should be larger than end_value

Public Sub example3()
    For i = 10 To 1 Step -1
        iArray(i) = i
    Next i
End Sub

Other Applications of For Loop

 Excel VBA change cell range to array

Excel VBA delete worksheet based on tab name

Outbound References

http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx

Excel VBA For Loop / For...Next