Exercise 6.03

The aim of this exercise is to loop over a range of cells in a collection of worksheets and build a list of films based on criteria selected by the user.

Files Needed

Click here to download the file needed for this exercise.

Instructions

1. Extract and open the Movies By Year.xlsm workbook.  Look at the Movies worksheet:

On the Movies sheet, the user can select a film genre and click Get Films

2. Look at some of the other worksheets:

Each of the other sheets contains a list of films made in a particular year.

3. Open the VBE and find the Get_Films subroutine in Module1.
4. Add code to the Get_Films subroutine to achieve the following:

• Loop over the collection of worksheets using a For Each loop.
• Check that the worksheet being processed is not the Movies worksheet.
• In each worksheet, loop over the range of cells starting at A2 and ending at the last populated cell in column A.
• For each cell, check if the value of the Genre column matches the value selected in cell B2 on the Movies worksheet.
• Copy the row of data into the list on the Movies worksheet.

5. Test that the procedure works by selecting a genre and clicking the Get Films button:

The list will be populated with films from the genre you have selected.

6. Save and close the workbook.

Answer Files

Click here to download a file containing a suggested answer.