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.


Example 1: Write a Macro in VBA by using For each loop to print each word of the array in a separate line.


VBA For Each Loop

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”


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.



Example 4: Write a macro, to calculate the positive, negative or zero in a sheet.


VBA For Each Loop 4

Pin It on Pinterest

Share This