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