Home and Learn - Free Excel VBA Course

5.2 For Each Loops in Excel VBA

For Each loops are normally used with collections and arrays (you'll learn about Arrays in a later lesson). This just means that you have one or more items to cycle through. It's still a For loop, though. But this time the word "Each" follows the word "For", with a space between the two. The Each part means "Each individual item in the collection or array". The structure of a For Each loop looks like this:

For Each variable_name In collection_name

Next variable_name

So you start with For Each. You then need a variable name. This can be almost anything you want, just like normal variables. VBA will store individual items into this variable. After your variable name you need the word "In". You then follow that with the name of the collection or array you're trying to access. The final line is the same as for normal For loops - Next variable_name.

As the code for the For Each loop, though, you do something with Each item stored in that variable_name. Let's see an example.

Suppose you have the following spreadsheet:

Excel spreadsheet showing values in cells

You want to remove the hyphens from each cell in the A column. To do this, you can use a For Each loop. The collection is the Range of cells A2 to A6. Each item is a cell in that range. The For Each loop would then start like this:

For Each MyCell In Range("A2:A6")

The variable name that will store individual cells from the range is called MyCell. (This is something we just made up. Like any other variable name, you can call it what you want.)

However, the variable name in a For Each Loop must be of the Variant type, or Object type. Just before the For Each Loop, then, you can add this:

Dim MyCell As Variant

Or you can add this:

Dim MyCell As Range

The collection after the word "In" is Range("A2:A6").

The end of the loop would then be:

Next MyCell

As the code for the For Each loop we can do this:

MyCell.Value = Replace(MyCell.Value, "-", "")

The variable called MyCell is now a cell in its own right. This means that MyCell will have a Value property - the contents of the cell. The first time round the loop this will be the contents of cell A2. After an equal sign, we're using the Replace function. We're replacing the hyphen with no space at all (this is done with two double quotes).

Here's the whole code to try:

Dim MyCell As Variant

For Each MyCell In Range("A2:A6")

MyCell.Value = Replace(MyCell.Value, "-", "")

Next MyCell

So with just a couple of lines of code we can cycle through all the cells in the A column, doing whatever we want with the contents of each cell. A pretty powerful technique, hey!

 

In the next lesson, we'll discuss the Cells property, which is useful when using loops.

< For Loops

Next Lesson: 5.3 Cells Property >