For...Next Statement

Description

Repeats a group of statements a specified number of times.

Syntax

For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]

The For...Next statement syntax has these parts:

Remarks

The step argument can be either positive or negative. The value of the step argument determines loop processing as follows:

Once the loop starts and all statements in the loop have executed, step is added to counter. At this point, either the statements in the loop execute again (based on the same test that caused the loop to execute initially), or the loop is exited and execution continues with the statement following the Next statement.

Tip

Changing the value of counter while inside a loop can make it more difficult to read and debug.

The Exit For can only be used within a For Each...Next or For...Next control structure to provide an alternate way to exit. Any number of Exit For statements may be placed anywhere in the loop. The Exit For is often used with the evaluation of some condition (for example, If...Then), and transfers control to the statement immediately following Next.

You can nest For...Next loops by placing one For...Next loop within another. Give each loop a unique variable name as its counter. The following construction is correct:

For I = 1 To 10
    For J = 1 To 10
        For K = 1 To 10
            . . .
        Next K
    Next J
Next I

Note

If you omit counter in a Next statement, execution continues as if you had included it. If a Next statement is encountered before its corresponding For statement, an error occurs.

See Also

Do...Loop Statement, Exit Statement, For Each...Next Statement, While...Wend Statement.

Example

This example uses the For...Next statement to create a string that contains 10 instances of the numbers 0 through 9, each string separated from the other by a single space. The outer loop uses a loop counter variable that is decremented each time through the loop.

For Words = 10 To 1 Step -1     ' Set up 10 repetitions.
    For Chars = 0 To 9    ' Set up 10 repetitions.
        MyString = MyString & Chars    ' Append number to string.
    Next Chars
    MyString = MyString & " "    ' Append a space.
Next Words

This example displays the number of columns in the selection on Sheet1. The code also tests for a multiple-area selection; if one exists, the code loops on the areas of the selection.

Worksheets("Sheet1").Activate
areaCount = Selection.Areas.Count
If areaCount <= 1 Then
    MsgBox "The selection contains " & _
        Selection.Columns.Count & " columns."
Else
    For i = 1 To areaCount
        MsgBox "Area " & i & " of the selection contains " & _
            Selection.Areas(i).Columns.Count & " columns."
    Next i
End If

This example creates a new worksheet and then inserts a list of the active workbook's sheet names in the first column.

Set newSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
    newSheet.Cells(i, 1).Value = Sheets(i).Name
Next i

This example selects every other item in list box one on Sheet1.

Dim items() As Boolean
Set lbox = Worksheets("Sheet1").ListBoxes(1)
ReDim items(1 To lbox.ListCount)
For i = 1 To lbox.ListCount
    If i Mod 2 = 1 Then
        items(i) = True
    Else
        items(i) = False
    End If
Next
lbox.MultiSelect = xlExtended
lbox.Selected = items