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