BYU Student Author: @TylerBooth
Reviewers: @klayton, @Mark, @Parker_Sherwood
Estimated Time to Solve: 45 Minutes
We provide the solution to this challenge using:
- Excel
- VBA
Need a program? Click here.
Overview
You work as a corporate accounting intern for a small, rapidly growing property management company in Utah. The company manages apartment complexes near major universities across the state. You enjoy your job, but there are some downsides to working for a smaller company. The biggest challenge you face is the lack of uniformity across the various software platforms that you need to use for your job.
For example, each week you are required to download a list of credit card transactions from the company’s bank and upload them into a separate billing platform so that the property management company can be reimbursed by the property. Unfortunately, the banking platform and the billing software cannot communicate with each other, so you must manually download the transactions, filter out the transactions that are not allocated to a property, and then re-upload the transactions that will be reimbursed. Although the company is still small and you can quickly use filter tools in Excel, you know that as the company grows, this task will become much more tedious.
It is unlikely that the company will be able to invest in more advanced software soon, but you know that there must be a better and faster way to filter the transactions with the technology that you currently have.
Instructions
The macro-enabled workbook for this challenge contains four sheets. The first is the “Controls” sheet. This sheet specifies that transactions assigned to “Corporate Office 1”, “Corporate Office 2”, and those not associated with a specific property will not be eligible for reimbursement. This sheet also includes two buttons that are used for controlling the other sheets. The second sheet is the “Transactions” sheet and will include all of the credit card transactions that need to be allocated between the third sheet “Reimburse” and the fourth sheet “No Reimbursements.” You should not manually enter any data into any of the sheets. To complete this challenge, you should:
- Click the “Generate Data” button on the “Controls” sheet. This will automatically populate the “Transactions” sheet with data.
- Select “Developer” from the ribbon and then “Visual Basic.” This will open the module with the VBA code that generated the credit card data. It also includes a new macro called, “AllocateData.” This is where you should write your code. For more information, read about the Developer Tab here: Show the Developer tab - Microsoft Support
- Your code should do the following:
- Copy the first row with the data headers to both the “Reimburse” and “No Reimbursement” sheet.
- Loop through all the transactions. If the transaction has no specified property, or is allocable to “Corporate Office 1” or “Corporate Office 2”, the row should be moved to the “No Reimbursement” sheet.
- All other rows should be moved to the “Reimburse” sheet.
- Finally, assign the “AllocateData” macro to the button labeled, “Allocate Data.”
Data Files
NOTE: After downloading the data file, right-click the file and select “Properties”. Near the bottom, check the box to unblock the file. Sometimes macro-enabled workbooks will be blocked for security reasons and will not allow you to open the file properly.
Suggestions and Hints
The data generated by the VBA code is random. Therefore, the data output to the “Reimburse” and “No Reimbursement” sheets will always be different than the output shown in the solution file. Check the solution file for an example of VBA code that will properly allocate the transactions.
Solution
