Excel-VBA Solutions: How to Use For Each Next in Excel VBA

For Each Next Statement is used to execute one or more statements on each item in a collection or array. So this is the syntax of the For Each Next statement.

For Each element in Collection/Array
Statements
Next

However if you want you can add the "element" in the "Next" line as well. So then the syntax would be like this.

For Each element in Collection/Array
Statements
Next element

Now let’s look at how to use this statement in programs. Consider this sample excel sheet.

We have values here and there in this worksheet. So in this example we are going to color the cells which have values greater than five in yellow color. So the end result should look like this.

To do that we need to execute some statements on all the cells in the used range. So we can do that using For Each Next statement as follows.

Sub ForEachExample()

     Dim Rng As Range

     For Each Rng In ActiveSheet.UsedRange
         If Rng.Value > 5 Then
             Rng.Interior.Color = vbYellow
         End If
     Next

End Sub

In the above code we execute the following statement on all the cells in the used range.

If Rng.Value > 5 Then
Rng.Interior.Color = vbYellow
End If

Also if we need, we can use “Next Rng” instead of “Next” as well. So then the For Each statement would look like this.

For Each Rng In ActiveSheet.UsedRange
     --------------------
     Statements
     --------------------
Next Rng

Next let’s look at how to use For Each statement for an array. I will explain it using the following Excel sheet.

This excel sheet has numbers and text in column A. We are going to write whether the value is numeric or non-numeric in column B. First we add the content of range A1:A10 to an array called “MyArray”. Then execute the set of statements on each element of the array. So the completed subroutine will look like this.

Sub ForEachExample2()

     Dim WS As Worksheet
     Dim MyArray() As Variant
     Dim elm As Variant
     Dim Counter As Integer

     Set WS = ActiveSheet
     MyArray = WS.Range("A1:A10")

     Counter = 1
     For Each elm In MyArray
         If IsNumeric(elm) = True Then
             WS.Range("B" & Counter).Value = "Numeric"
         Else
             WS.Range("B" & Counter).Value = "Non-Numeric"
         End If
         Counter = Counter + 1
     Next

End Sub

Here following statements are executed on each element of the array.

If IsNumeric(elm) = True Then
WS.Range("B" & Counter).Value = "Numeric"
Else
WS.Range("B" & Counter).Value = "Non-Numeric"
End If
Counter = Counter + 1

Subroutine has used IsNumeric function to check whether the value is numeric or not. So this would be the result of the above macro.

Note that if we use For Each Next statement for collection, then the element variable should be either variant or an object. However if we use that statement for an array then the element variable should be only a variant.

You can also use “Exit For” inside the For Each Next statement to exit the loop when a certain condition is met after the evaluation. Consider this sample excel sheet. This sheet contains 8 colors in row A. What we are going to do is find where the word “Yellow” is in, and then output the row number of that cell.

We can do that using the code below. Here we have used a custom range instead of UsedRange.

Sub ForEachExample3()

     Dim DataRange As Range
     Dim Rng As Range

     Set DataRange = ActiveSheet.Range("A1:A8")
     For Each Rng In DataRange
         If Rng.Value = "Yellow" Then
             MsgBox "Yellow is in row " & Rng.Row
             Exit For
         End If
     Next Rng

End Sub

This is the result of the subroutine “ForEachExample3”.

You can use “Exit For” anywhere inside the For Each Next statement depending on your requirement. If a program executes to the "Exit For" then it will give control to the statement immediately following the “Next”. In the above example to the "End Sub" as there are no any other statements.

Pages

How to Use For Each Next in Excel VBA