Skip to main content

For Each...Next statement

Table of contents
  1. Syntax
  2. Remarks
  3. Examples

Repeats a group of statements for each element in an array or collection.


For Each element In group
[ statements ]
[ Exit For ]
[ statements ]
Next [ element ]

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

Part Description
element Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.
group Required. Name of an object collection or array (except an array of user-defined types).
statements Optional. One or more statements that are executed on each item in group.


The For…Each block is entered if there is at least one element in group. After the loop has been entered, all the statements in the loop are executed for the first element in group. If there are more elements in group, the statements in the loop continue to execute for each element. When there are no more elements in group, the loop is exited and execution continues with the statement following the Next statement.

Any number of Exit For statements may be placed anywhere in the loop as an alternative way to exit. Exit For is often used after evaluating some condition, for example If…Then, and transfers control to the statement immediately following Next.

You can nest For...Each...Next loops by placing one For…Each…Next loop within another. However, each loop element must be unique.

You can't use the For...Each...Next statement with an array of user-defined types because a Variant can't contain a user-defined type.


The following procedure adds 10 to the value of every cell in the range A1 to A10.

Sub Add10ToAllCellsInRange()
    Dim rng As Range
    For Each rng In Range("A1:A10")
        rng.Value = rng.Value + 10
End Sub

The following code loops through each element in an array and sets the value of each to the value of the index variable i.

Sub ArrayTest()
    Dim arr(10) As Integer, item As Variant, i As Integer
    For Each item In arr
        arr(i) = i
        Debug.Print arr(i)
        i = i + 1
End Sub

The following example tests for the first cell in the range A1:B5 that does not contain a number. If such a cell is found, a message is displayed and Exit For exits the loop.

Sub TestForNumbers()
    For Each rng In Range("A1:B10")
        If IsNumeric(rng.Value) = False Then
            MsgBox "Cell " & rng.Address & " contains a non-numeric value."
            Exit For
        End If
End Sub

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>