Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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:

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 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:

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:

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:

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:

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 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:

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:

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 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:

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:

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

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

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

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

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

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

Return to Excel and test that the code works:

Select a year and click the Highlight Flops button.

Check that the results are as expected:

Even Keanu couldn't save that one.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Specifying a Range to Loop Over

Referring to Each Cell

Using an Object Variable

Head office

London

Manchester