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.
Click here to download the file needed for this exercise.
- 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
- Look at some of the other worksheets:
Each of the other sheets contains a list of films made in a particular year.
- Open the VBE and find the Get_Films subroutine in Module1.
- 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.
- 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.
- Save and close the workbook.
Click here to download a file containing a suggested answer.