Looping Through Cells with For Each

1. Understanding the "For Each" Loop

Imagine you have a stack of invoices and you need to check each one for a signature. You would pick up the first invoice, check it, put it aside, pick up the next one, and repeat until the stack is empty. A For Each loop works in the exact same way. It goes through a collection of items (like a range of cells) and performs an action on each item, one by one, until it has gone through all of them.

This is incredibly efficient because you don't need to know in advance how many items are in the collection. The loop handles it for you.

The basic syntax looks like this:

For Each [item] In [collection]
    ' Code to execute for each item
Next [item]

• [collection] is the group of objects you want to work with, such as a range of cells.
• [item] is a variable that represents the single object the loop is currently focused on during each pass (e.g., one specific cell).

Let's begin by reading a short overview that explains the concept and syntax.

2. Your First Loop: Iterating Over a Fixed Range

Let's put this into practice with a hands-on example. We'll write a macro that loops through a specific, fixed range of cells (e.g., A1 to A5) and performs a simple mathematical operation on each one.

This requires two key steps:

1. Declare variables: We need one Range variable to hold the entire collection of cells, and another Range variable to represent the single cell we're working on in each iteration of the loop.
2. Write the loop: We use the For Each...Next structure to process each cell within our defined range.

The following guide provides a very clear, step-by-step example of this process.

Following that guide, your code to double the value in each cell from A1 to A5 would look like this. Open the VBA Editor (Alt + F11), insert a new module, and try typing this out:

Option Explicit

Sub DoubleValues()

    ' 1. Declare variables
    Dim dataRange As Range
    Dim cell As Range

    ' 2. Define the collection of cells to loop through
    Set dataRange = ActiveSheet.Range("A1:A5")

    ' 3. Start the loop
    For Each cell In dataRange
        ' 4. Perform an action on the current cell
        ' Check if the cell contains a number before multiplying
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
            cell.Value = cell.Value * 2
        End If
    Next cell

End Sub

Before running this, enter some numbers into cells A1 through A5 on your worksheet. Then, run the DoubleValues macro. You'll see each number instantly double. The IsNumeric check is good practice to prevent errors if a cell contains text.

3. Combining Loops with Conditionals

The true power of loops is unlocked when you combine them with the If statements you learned in the last lesson. This allows you to analyze each cell and perform an action conditionally.

For example, you could loop through thousands of sales figures and automatically highlight any that are below a certain target. Let's see how this is done. We'll create a macro that checks each cell in a range and changes the background color if the cell is blank.

Let's adapt that logic for a common freelance task: flagging negative numbers in a financial report. Here’s how you could write a macro to find all negative numbers in a range and color them red.

Sub HighlightNegativeValues()

    Dim reportData As Range
    Dim cell As Range

    Set reportData = ActiveSheet.Range("C3:H12")

    For Each cell In reportData
        ' Use IsNumeric to avoid errors on text cells
        If IsNumeric(cell.Value) Then
            ' The condition from our last lesson!
            If cell.Value < 0 Then
                ' The action: change the cell's interior color
                cell.Interior.Color = vbRed
            End If
        End If
    Next cell

End Sub

This pattern—looping through a range and using an If statement to test each cell's value—is one of the most common and useful techniques in VBA automation.

4. Looping Over Dynamic Ranges

So far, we've used a fixed range like Range("C3:H12"). But what if your data changes size? Hard-coding ranges can be inflexible. A more robust approach is to use dynamic ranges that adapt to the data.

There are two very common ways to do this:

1. Selection: This refers to whatever range of cells the user has currently highlighted with their mouse. This is great for creating tools that let the user decide what data to act upon.
2. ActiveSheet.UsedRange: This is a special property that automatically refers to the entire rectangle of cells on the active worksheet that contains data. This is perfect for when you need to process all the data on a sheet, regardless of its size.

Let's see how to use UsedRange to create a macro that counts all the cells containing a formula on your sheet.

Notice how simple it is to change the collection the loop works on. To modify our earlier HighlightNegativeValues macro to work on the user's selection, you would only need to change one line:

From:
Set reportData = ActiveSheet.Range("C3:H12")

To:
Set reportData = Selection

And the loop For Each cell In reportData would work exactly the same way, but now on the selected cells instead of a fixed range.

Conclusion

In this lesson, you've added a powerful tool to your VBA toolkit: the For Each loop. You now have the ability to automate tasks across large sets of data, moving you significantly closer to your goal of building professional-grade Excel solutions.

Key Takeaways:

• A For Each loop iterates over a collection of objects, like cells in a range, and executes a block of code for each one.
• The basic syntax is For Each item In collection ... Next item.
• You must declare a variable (e.g., Dim cell As Range) to represent the single item being processed in each pass of the loop.
• Combining For Each loops with If...Then statements allows for powerful conditional automation, such as formatting cells that meet specific criteria.
• Using dynamic ranges like Selection or ActiveSheet.UsedRange makes your macros more flexible and robust than using hard-coded ranges.

We've been working with objects like Range and ActiveSheet quite a bit. In the next lesson, we will formalize this by diving deeper into the VBA Object Model. You'll learn the hierarchy of these objects and how to reference and manipulate them with precision, giving you even greater control over Excel.