The aim of this exercise is to create a list of films by copying data from multiple worksheets according to criteria set by the user.
Click here to download the file needed for this exercise.
- Extract and open the Movies Yearly Data.xlsm workbook. You'll find a worksheet which allows you to enter a minimum and maximum run time for films whose details you want to copy:
Clicking the Get List button on the Movies worksheet should copy each film whose run time falls between the minimum and maximum entered from each of the other worksheets.
- In the VBE, find the Get_List_Of_Films subroutine and write code which will copy the matching films into the Movies worksheet. The suggestions below should help you to plan this procedure:
- Ensure that the procedure starts with the Movies worksheet selected.
- Use a conditional loop to move to the next worksheet until there are no more worksheets to select.
- Within the loop, select the next worksheet then select the first cell in the list of data on that sheet.
- Write a new conditional loop within the first which moves down the list of films on the selected worksheet.
- For each film, check if the run time is between the minimum and maximum entered on the Movies worksheet.
- If so, copy the row of data to the end of the current list on the Movies worksheet.
- After the loops have finished, select the Movies worksheet to see the list of results.
- Enter some values for the minimum and maximum run times and test that you see sensible results:
Check that your results match the criteria you have entered.
- Add If statements at the beginning of the Get_List_Of_Films procedure to prevent the user continuing if they have entered silly values. You could check the following:
- The user has entered a value for both the minimum and maximum run time.
- Both values are numbers.
- Both values are not less than zero.
- The maximum is not less than the minimum.
- Test that your validation code works:
It's helpful to tell the user what they have done wrong.
- Save and close the workbook.
Click here to download a file containing a suggested answer.