Using For...Next statements

Use For...Next statements to repeat a block of statements a specific number of times. For loops use a counter variable whose value is increased or decreased with each repetition of the loop.

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.

Sub Beeps() 
    For x = 1 To 50 
        Beep 
    Next x 
End Sub

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

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, the If…Then…Else statement is False, and the loop will continue to run as expected.