Description
Repeats a group of statements for each element in an array or collection.
Syntax
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
The For Each...Next statement syntax has these parts:
Part |
Description |
element |
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 OLE Automation object variable. For arrays, element can only be a Variant variable. |
group |
Name of an object collection or array (except an array of user-defined types). |
statements |
One or more statements that are executed on each item in collection. |
Important
When using For Each... Next with arrays, you can only read the value contained in the array elements indicated by the control variable element. You cannot set the value by assigning a value to element.
Remarks
The For Each block is entered if there is at least one element in group. Once the loop has been entered, all the statements in the loop are executed for the first element in group. Then, as long as 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.
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 Each...Next loops by placing one For Each...Next loop within another. However, each loop element must be unique.
Note
If you omit element 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.
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.
See Also
Do...Loop Statement, Exit Statement, For...Next Statement, While...Wend Statement.
Example
This example uses the For Each...Next statement to search the Text property of all elements in a collection for the existence of the string "Hello". In the example, MyObject is a text-related object and is an element of the collection MyCollection. Both are generic names used for illustration purposes only.
Found = False ' Initialize variable. For Each MyObject In MyCollection ' Iterate through each element. If MyObject.Text = "Hello" Then ' If Text equals "Hello". Found = True ' Set Found to True. Exit For ' Exit loop. End If Next
This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces the value with 0 (zero).
For Each c in Worksheets("Sheet1").Range("A1:D10") If c.Value < .001 Then c.Value = 0 End If Next c
This example loops on the range named "TestRange" and displays the number of empty cells in the range.
numBlanks = 0 For Each c In Range("TestRange") If c.Value = "" Then numBlanks = numBlanks + 1 End If Next c MsgBox "There are " & numBlanks & " empty cells in this range."
This example closes and saves changes to all workbooks except the one that is running the example.
For Each w In Workbooks If w.Name <> ThisWorkbook.Name Then w.Close savechanges:=True End If Next w
This example deletes every worksheet in the active workbook without displaying the confirmation dialog box.
Application.DisplayAlerts = False For Each w In Worksheets w.Delete Next w Application.DisplayAlerts = True
This example creates a new worksheet and then inserts a list of all names in the active workbook, including their formulas in A1-style notation in the language of the user.
Set newSheet = ActiveWorkbook.Worksheets.Add i = 1 For Each nm In ActiveWorkbook.Names newSheet.Cells(i, 1).Value = nm.NameLocal newSheet.Cells(i, 2).Value = "'" & nm.RefersToLocal i = i + 1 Next nm