Used to Loop Through a Collection or Array
Category: [Excel] Demo Available
For Each Loop
Used to Loop Through a Collection or Array
Got any Excel Questions? Free Excel Help
For Each Loop Step
A For Each Loop is used to loop through each element is a Collection, or an Array. A Collection is an object that contains a set of related objects. An Array is a set of sequentially indexed elements having the same intrinsic data type. To stop an endless loop, press ESC or CTRL+BREAK
To get the gist of what the example below is doing fill a few rows in Column "A" of the active sheet with some values. We could force an Exit from the For Each...Loop via the Exit For Statement. E.g. If rCell=10 Then Exit For
Sub For_Each_Collection()
Dim rRange As Range
Dim rCell As Range
Set rRange = Range("A1", Range("A65536").End(xlUp))
For Each rCell In rRange
MsgBox rCell.Value
Next rCell
End Sub
Sub For_Each_Array()
Dim lArray(10) As Long
Dim lArr
Dim lCount As Long
'Fill Array
For lCount = 0 To 10
lArray(lCount) = lCount
Next lCount
lCount = 0
'Show each value in Array
For Each lArr In lArray
MsgBox "The number " & lCount & " element in lArray is " & lArr
lCount = lCount + 1
Next lArr
End Sub
For...Next Loop
Syntax
For counter = Start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]
See also:
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.