VBA For Each Loop

An example of For Each with cell range

Let us start our example with the range of cells in the For Each loop. First, have a look at our sample sheet that we will use for our example:

• We will create a range of six cells (B2 to B7) and use them in the For Each.
• Inside the For Each, we will get the value of each cell and concatenate the values in a String variable.
• Finally, we will display a MsgBox with the values of all cells.

VBA code with For Each loop

Sub each_ex()
Dim SheetRange As Range
Dim cell
Dim concat_str As String

'Our collection to be used in For Each
Set SheetRange = Range("B2:B7")

For Each cell In SheetRange
 concat_str = concat_str & cell & ", "
Next cell

'Display concatenated cells

    MsgBox concat_str
End Sub

Output:

Syntax of For Each loop

So, based on our above example, you can notice the syntax of using For..Each .. Next loop:

For Each element In group

[ statements ]

[ Exit For ]

[ statements ]

Next [ element ]

An example of using For Each with an array

For this example, we have created an array of programming languages. In the For..Each loop, that iterates through each element of the array, we will display a MsgBox with the current array element value.

This should make your For Each concept clearer as the MsgBox statement is written once and you can see it executes for each item separately i.e. message box appears four times as you execute this code:

VBA code:

Sub each_ex()
Dim Langs
Dim item

'Creating an array of four elements
Langs = Array("C#", "Java", "C++", "Python")

'Loop will display Message Box four time as we have four array elements

For Each item In Langs

    MsgBox item

Next item


End Sub

Iterate through an array partially

Rather than iterating through each element of the array as using For Each loop, you may go through the array partially.

The example below shows how the If condition is used in our array:

The code:

Sub each_ex()

   Dim Langs(1 To 4) As String
   Dim concat_str As String
   Dim curr_item As Variant

   'Array Elements

   Langs(1) = "C#"
   Langs(2) = "Java"
   Langs(3) = "C++"
   Langs(4) = "Python"

   'Using the For Next loop and exit as current language is “C++”

   For Each curr_item In Langs

       If curr_item = "C++" Then

        Exit For

       End If

     concat_str = concat_str & curr_item & ", "

   Next curr_item

   MsgBox concat_str

End Sub

Output:

You can see that only two languages are displayed.

How to omit only the current iteration solution

Though the continue statement is not available in VBA, as in other programming languages. We can omit the current element (without executing any code) by using another way (which is the purpose of the continue statement).

So, again consider our array that has four languages.

In the example below, we will omit “C++” and display the other three languages (elements).

VBA code:

Sub each_ex()

   Dim Langs(1 To 4) As String
   Dim concat_str As String
   Dim curr_item As Variant

   'Array Elements
   Langs(1) = "C#"
   Langs(2) = "Java"
   Langs(3) = "C++"
   Langs(4) = "Python"


   'Omit loop only when condition is True

   For Each curr_item In Langs

       If curr_item = "C++" Then

        'Do Nothing

       Else

        concat_str = concat_str & curr_item & ", "

       End If

   Next curr_item

   MsgBox concat_str

End Sub

Result:

You can see that though C++ is our third element, we did not display it in the message box.