The aim of this exercise is to loop over a list of films and use conditional functions to calculate values for each row.
Click here to download the file needed for this exercise.
- Extract and open the Movies.xlsm workbook to find a worksheet containing a list of films:
The first three films in the list are shown here.
- Create a subroutine which begins by selecting cell A2.
- Add a conditional loop to the subroutine which selects the cell below the active cell and continues doing so until a blank cell is selected.
- Within the loop, use an IIf function to assign a value to the Success column (column D) based on the number of Oscars a film has won:
|1 or more||Winner|
- Within the loop, use a Switch function to assign a value to the Length column (column F) based on the Run Time of a film:
|100 - 149||Medium|
|150 - 199||Long|
- Run the subroutine you have written and check that you see the expected results:
A selection of results from the list.
- Save and close the workbook.
Click here to download a file containing a suggested answer.