For Each loop

For Each

This Loop is slightly different from the others, but only in the fact that it requires an Object as the Variable. What it does is simply Loop though each single Object in a Collection of Objects.

 

Sub My_ForEach()

Dim rMyCell As Range

Dim iMyNumber As Integer

For Each rMyCell In Range(“A1:A100”)

iMyNumber = 1 + iMyNumber

rMyCell.Value = iMyNumber

Next rMyCell

End Sub

 

For Each Cell in the Range A1 to Range A100 add 1 to the Variable iMyNumber2 Where “Cell” is represented by the Range Variable “rMyCell” So it will do this 100 times as there are 100 Range Objects in the Object Collection Range(“A1:A100”)

 

We do not need to tell the For Each Loop how many times to Loop as it already knows how many Objects (Cells in this case) there are in the Object Collection (Range(A1:A100)).

 

Our Object Collection does not have to be a Range Collection, it could be a Charts, Worksheets, Workbooks etc Collection. In fact it can be any Collection of Objects. So if we wanted to Loop through all Worksheets in a Workbook we could use:

 

Sub My_ForEach1()

Dim wWsht As Worksheet

For Each wWsht In ThisWorkbook.Worksheets

wWsht.Range(“A1”) = wWsht.Name

    Next wWsht

End Sub