Module 6 - Collections and Loops
Lesson 6.3 - For Each Loops
Topic 6.3.2 - Looping Through a Range

You can treat a range of cells as a collection and use a For Each loop to process each cell individually.  This part of the lesson shows you how!

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a worksheet containing a basic menu which allows you to select a year from a drop down list and then click one of two buttons:

Basic menu

Select a year from the drop down list and then click a button.

 

Clicking a button will take you to the appropriate worksheet which shows a list of hit movies or flops:

Hits

The Hits sheet shows a list of the highest grossing movies.

 

We'd like to loop over the list of films on the worksheet and highlight those which were released in the year which was selected on the Menu sheet.

Specifying a Range to Loop Over

Open the VBE and find the subroutine called Highlight_Hits in Module1:

Subroutine

The procedure stores the selected year in a variable then selects the Hits worksheet and calls another subroutine to clear any existing highlighting.

 

We want to loop over a range of cells using a For Each loop.  This means that we need a variable to hold a reference to a single range object each time the loop is processed.  Declare a Range variable at the top of the subroutine:

Range variable

You can give your variable a different name if you like.

 

Now we can create a For Each loop which uses this variable.  Begin writing the loop at the end of the subroutine:

Begin For Each

Begin writing the For Each loop in the usual way, using the variable that you declared earlier.

 

In the previous part of the lesson, the For Each loops we created referred to a collection such as Worksheets or Charts.  When you loop over a range of cells you need to reference the range whose individual cells you want to process.  In the Hits worksheet we want to look at each cell from A3 to A52:

Specify range

You can refer to a range of cells instead of a collection.

 

If you aren't sure how long the list will be, you can use the End property to find the bottom cell in the list.  Replace the code shown above with that shown below:

Loop

This loop will process each cell between the top and the bottom of the list (providing there are no gaps in it).

Referring to Each Cell

Now that the loop is configured, we can refer to each individual cell in the list using the Range variable.  The first thing we would like to do inside the loop is test if the value of the cell to the right of the film's title is equal to the value stored in the YearChosen variable.  Ad an If statement within the loop to do this:

If statement

Use the Range variable you declared earlier to refer to the cell containing the film's title. Use the Offset property to reference the cell one column to the right.

If the condition is met, we'd like to change the fill colour of each populated cell in the same row.  Add an instruction within the If statement to do this:

Colour

Use the End property to find the last populated cell in the row.

You can now test the code works by returning to the Menu sheet in Excel:

Select year

Select a year and then click the Highlight Hits button.

 

After clicking the button you'll find that films released in the selected year will be highlighted on the Hits sheet:

Results

The result of clicking the Highlight Hits button.

Using an Object Variable

Rather than referring to the range of cells in the For Each statement, you can instead use an object variable.  Declare a second Range variable at the top of the subroutine:

Second variable

This second variable will hold a reference to the block of cells containing film names.

 

Before beginning the For Each loop, assign a reference to the relevant range of cells in the variable you have just declared:

Set variable

Set the FilmList variable to refer to the list of cells you want to loop through.

You can now alter the For Each statement to reference the variable:

Ater For Each

Reference the FilmList variable in the For Each statement.

This won't make any difference to the end-user experience but it's a more elegant way to write the loop!

To practise looping over a range of cells:

  1. Find the subroutine called Highlight_Flops in Module1:

Sub Highlight_Flops()

 

Dim YearChosen As Integer

 

YearChosen = Range("B3").Value

 

Worksheets("Flops").Select

Clear_Highlighting

 

End Sub

  1. Add two Range variables to the subroutine:

Sub Highlight_Flops()

 

Dim YearChosen As Integer

Dim r As Range

Dim FlopList As Range

 

YearChosen = Range("B3").Value

 

Worksheets("Flops").Select

Clear_Highlighting

 

End Sub

  1. Add an instruction a the end of the procedure to assign a reference to the FlopList variable:

Sub Highlight_Flops()

 

Dim YearChosen As Integer

Dim r As Range

Dim FlopList As Range

 

YearChosen = Range("B3").Value

 

Worksheets("Flops").Select

Clear_Highlighting

 

Set FlopList = Range("A3", Range("A2").End(xlDown))

 

End Sub

  1. Create a For Each loop to process each cell in the FlopList range:

Sub Highlight_Flops()

 

Dim YearChosen As Integer

Dim r As Range

Dim FlopList As Range

 

YearChosen = Range("B3").Value

 

Worksheets("Flops").Select

Clear_Highlighting

 

Set FlopList = Range("A3", Range("A2").End(xlDown))

 

For Each r In FlopList

 

Next r

 

End Sub

  1. Add an If statement within the loop to check if the film was released in the same year the user has selected:

Sub Highlight_Flops()

 

Dim YearChosen As Integer

Dim r As Range

Dim FlopList As Range

 

YearChosen = Range("B3").Value

 

Worksheets("Flops").Select

Clear_Highlighting

 

Set FlopList = Range("A3", Range("A2").End(xlDown))

 

For Each r In FlopList

 

If r.Offset(0, 1).Value = YearChosen Then

 

End If

 

Next r

 

End Sub

  1. Add an instruction within the If statement to change the fill colour of all populated cells on the same row:

Sub Highlight_Flops()

 

Dim YearChosen As Integer

Dim r As Range

Dim FlopList As Range

 

YearChosen = Range("B3").Value

 

Worksheets("Flops").Select

Clear_Highlighting

 

Set FlopList = Range("A3", Range("A2").End(xlDown))

 

For Each r In FlopList

 

If r.Offset(0, 1).Value = YearChosen Then

 

Range(r, r.End(xlToRight)).Interior.Color = rgbAqua

 

End If

 

Next r

 

End Sub

  1. Return to Excel and test that the code works:
Test

Select a year and click the Highlight Flops button.

 
  1. Check that the results are as expected:
Flops

Even Keanu couldn't save that one.

  1. Save and close the workbook.
This page has 0 threads Add post