Using For...Next Statements
Last modified: December 06, 2011
Applies to: Office 2013 | VBA
The following procedure makes the computer beep 50 times. The For statement specifies the counter variable and its start and end values. The Next statement increments the counter variable by 1.
Using the Step keyword, you can increase or decrease the counter variable by the value you specify. In the following example, the counter variable j is incremented by 2 each time the loop repeats. When the loop is finished, total is the sum of 2, 4, 6, 8, and 10.
Sub TwosTotal() For j = 2 To 10 Step 2 total = total + j Next j MsgBox "The total is " & total End Sub
To decrease the counter variable, use a negative Step value. To decrease the counter variable, you must specify an end value that is less than the start value. In the following example, the counter variable myNum is decreased by 2 each time the loop repeats. When the loop is finished, total is the sum of 16, 14, 12, 10, 8, 6, 4, and 2.
Sub NewTotal() For myNum = 16 To 2 Step -2 total = total + myNum Next myNum MsgBox "The total is " & total End Sub
It's not necessary to include the counter variable name after the Next statement. In the preceding examples, the counter variable name was included for readability.
You can exit a For...Next statement before the counter reaches its end value by using the Exit For statement. For example, when an error occurs, use the Exit For statement in the True statement block of either an If...Then...Else statement or a Select Case statement that specifically checks for the error. If the error doesn't occur, then the If…Then…Else statement is False, and the loop will continue to run as expected.