Excel VBA For Each Loop
A For Each loop executes a statement or a group of statements for each element in an array or collection. It repeats the statement/condition/code for each element in a collection. For Each Loops loop through every object in an array, such as every worksheet in workbook or every cell in a range.
The “For Each” loop is similar to For Next Loop; however, it is executed for each element in an array or collection. The difference between both of them is that the step counter won't exist in this loop. It is mostly used with collections, arrays or used in the context of the File system objects in order to carry out recursively.
Syntax
For Each element In Group [statement 1] [statement 2] .... [statement n] [Exit For] [statement n1] [statement n2] Next
Example 1: Write a Macro in VBA by using For each loop to print each word of the array in a separate line.
Sub ForEach_Loop_Example1()
'programming language is an array
language = Array("VBA", "Java", "C#", "Python")
Dim languagenames As Variant
'iterating using For each loop.
For Each Item In language
languagenames = languagenames & Item & Chr(10)
Next
MsgBox language
End Sub
Output
Example 2: Write a procedure or macro using For each that does the following task:
Get "Qualified" in column E if the value of column D is above 200 else it should return “Not Qualified”
Sub ForEach_Loop_Example2()
Dim rng As Range
Dim cell As Range
Set rng = Range("D2:D12")
'execute a statement or a group of statements for each element
For Each cell In rng 'rng is a Range collection and For-Each is moving into each cell of rng collection
If cell > 200 Then
Cells(cell.Row, 5) = "Qualified"
Else
Cells(cell.Row, 5) = "Disqualified"
End If
Next
End Sub
Output
| Name | Title | Date | Sale Amount | Qualified/ Not Qualified |
| Itrat Zaidi | IT Manager | 6/8/2016 | $220.00 | Qualified |
| Varun Mahanty | Sales | 1/1/2014 | $251.00 | Qualified |
| Elmer C. Laslo | Customer Service | 6/16/2016 | $217.00 | Qualified |
| Sohrab Grait | Data Entry Specialist | 5/31/2015 | $344.00 | Qualified |
| Gerard M. Banmiller | Customer Service | 9/8/2014 | $980.00 | Qualified |
| Arvind Patra | IT Manager | 11/22/2015 | $178.00 | Disqualified |
| John C. Black | Customer Service | 3/31/2014 | $258.00 | Qualified |
| N. William White | Data Entry Specialist | 12/21/2015 | $38.00 | Disqualified |
| Robert F. Mangano | Data Entry Specialist | 7/16/2015 | $371.00 | Qualified |
| Rohit Thakkar | IT Tech | 12/21/2017 | $67.00 | Disqualified |
| Thomas E. Vessey | Customer Service | 1/1/2017 | $437.00 | Qualified |
Nested For each Loop
You can nest For Each loop by putting one loop within another. When you nest loops, each loop must have a unique variable.
Example 3: Write a macro or procedure, by using nested for each loop, print the values of one array repeatedly with second array.
'For each repeats the statement/condition/code for each element in a collection
Sub ForEach_Loop_Example3()
'initializing the array
numbers = Array(1, 2, 3)
letters = Array("a", "b", "c")
Dim n As Variant
Dim Row As Integer, Col As Integer
Row = 1
Col = 1
' Iterate through the list by using nested loops.
For Each Item In numbers
For Each letter In letters
n = Item & letter & Chr(10)
Cells(Row, Col) = n
Row = Row + 1
Next
Next
End Sub
Output
1a 1b 1c 2a 2b 2c 3a 3b 3c
Example 4: Write a macro, to calculate the positive, negative or zero in a sheet.
'For each repeats the
statement/condition/code for each element in a collection
Sub ForEach_Loop_Example4()
Dim num As Range
For Each num In Range("A2:A11")
If num.Value > 0 Then
num.Offset(0, 1).Value = "Positive"
ElseIf num.Value < 0 Then
num.Offset(0, 1).Value = "Negative"
Else
num.Offset(0, 1).Value = "Zero"
End If
Next num
End Sub
Output
Number Positive/Negative 56 Positive 0 Zero 65 Positive -98 Negative 87 Positive 0 Zero -54 Negative -9 Negative 2 Positive 0 Zero
