For Each Loop (Excel VBA)

For Each loop is for looping through each element in a collection. It is especially useful when you don’t know how many elements there is to loop through.

*Please note that I don’t claim that code examples are the most efficient or best for the jobs that theya re written for. They are presented for explainatoy purposes only.

Syntax for For Each loop is :

For Each element [ As datatype ] In group

[ statements ]

[ Continue For ] optional

[ statements ]

[ Exit For ] optional

[ statements ]

Next [ element ]

Here is an example use of For Each Loop:

‘              This loop finds and counts the cells with cell value is “4” in Column A.

Sub ForEach_Example()

Dim cell As Range

Dim counter As Integer

counter = 0

For Each cell In Range(“A:A”)

If cell.Value = 4 Then

counter = counter + 1

End If

Next cell

MsgBox counter

End Sub

For more Excel VBA related tutorials, please visit VBA Category.