Choose your language

VBA For...Next


The For statement ending with Next is used to repeat a code located between these two statements for a given number of times.

Code repetition instructions are also called loop.

Instead of writing like this:

Sub ForNext1()
    i = 1
    MsgBox "Repetition of the code for N. " & i
    i = i + 1
    MsgBox "Repetition of the code for N. " & i
    i = i + 1
    MsgBox "Repetition of the code for N. " & i
    'The Msgbox will pop-up 3 times
End Sub

We can write like this:

Sub ForNext1()
    For i = 1 To 3 'i starts at 1 and goes up to 3
        Msgbox "Repetition of the code for N. " & i
    Next i
    'The Msgbox will pop-up 3 times
End Sub

The For Next statement needs a counter, in this case we used i, which starts at 1. At each command Next i the value of i will increase one unit. The last For repetition will be performed when i equals 3.

The variable used as a counter is chosen by the user and preferably has been previously declared.

Try running the above code in VBE by successively pressing F8. Hover the mouse over the i at each repetition to see the instant value of the variable.

Step Into VBE

VBA For Next Step

By default, for every Next i statement in the examples above, a value of +1 is added to the value of i, as in i=i+1.

We can customize this increment with the Step argument.

Sub ForNext1()
    For rep = 10 To 14 Step 2
        Msgbox "Hello VBA World!"
        Msgbox " Repetition " & rep & "."
    Next rep
End Sub

In this example, "Hello VBA World!" was repeated 3 times, starting with rep as 10 and adding plus 2 at each iteration until its value reaches 14, the last iteration. 10, 12 and 14.

In this case, the amount of iterations can be calculated by $({14-10 \over 2}+1)=3$.

Iteration is the name given to one of the many repetitions that a loop can have.

It is possible to assign negative values to Step.



SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2019 SuperExcelVBA | ABOUT

Protected by Copyscape