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