Using For Each...Next Loops

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 .

Listing 6.13 shows the resulting procedure, ConvertToProper . Note that this procedure uses the Selection object to represent the currently selected range.

Listing 6.13. 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 use the Tools, Macro, Macros command to find and run the ConvertToProper procedure.