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.
