Ozgrid, Experts in Microsoft Excel Spreadsheets

For Each Loop


Used to Loop Through a Collection or Array

Back To: Excel VBA Loops . Lot's of free Excel VBA . Got any Excel Questions? Free Excel Help See Also: Loop Through Worksheets

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

For counter = Start To end [Step step]
[Exit For]
Next [counter]

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software


Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates