Using For Each...Next Loops

A useful variation of the For...Next loop is the For Each...Next loop, which operates on a collection of objects.

You don't need a loop counter because VBA just loops through the individual elements in the collection and performs on each element whatever operations are inside the loop.

Here's the structure of the basic For Each...Next loop:


For Each element In collection

    [statements]

Next [element]

element
A variable used to hold the name of each element in the collection.

collection
The name of the collection.

statements
The statements to be executed for each element in the collection.

As an example, let's create a command procedure that converts a range of text into proper case (that is, the first letter of each word is capitalized). This function can come in handy if you import mainframe text into your worksheets because mainframe reports usually appear entirely in uppercase. This process involves three steps:

Loop through the selected range with For Each...Next.
Convert each cell's text to proper case. Use Excel's Proper() worksheet function to handle this:
WorksheetFunction(Proper(text))
text The text to convert to proper case.
Enter the converted text into the selected cell. This is the job of the Range object's Formula method:
object.Formula = expression
object
The Range object in which you want to enter expression.

expression
The data you want to enter into object.

A Sub Procedure That Uses For Each...Next to Loop Through a Selection and Convert Each Cell to Proper Text

Sub ConvertToProper()
    Dim cellObject As Range
    For Each cellObject In Selection
        cellObject.Formula = WorksheetFunction(Proper(cellObject.Formula))
    Next
End Sub

How would you use this procedure in practice? You'd highlight the cells you want to convert and then choose the Developer, Macros command to find and run the ConvertToProper procedure.

[Previous] [Contents] [Next]

Using For Each...Next Loops

In this tutorial: