HW#16 - Job Billing Database

The following spreadsheet is used to manage the jobs associated with a local crane company. The table at the bottom ("CLIENT LIST") represents the clients of the company. The date in the Paid Thru column of the client list represents the last date at which the client paid outstanding invoices and was fully paid up. Any jobs for the company after that date are not yet paid. The table at the top represents a list of recent jobs, some of which may yet be unpaid.

There are two worksheets associated with this workbook. The second worksheet is shown in the following figure. The client name and address at the top of the invoice are automatically updated using VLOOKUP functions in Excel formulas. But the contents of the table must be populated from VB code.

The objective of this exercise is to write code for the Build Invoice button so that it searches through the Job Archive table and finds all jobs associated with the selected client that are not yet paid and compiles a list of unpaid jobs on the invoice sheet. This sheet can then be printed and sent to the client.

Click here to download a copy of the spreadsheet. Then do the following:

a. Enter a formula for the Total column (H9:H32) on the Jobs page that computes the job total as the Hours times the Rate.

b. Write the code for the Build Invoice button. The code should do the following:

(i) Clear the contents of the invoice table (A9:G34).

(ii) Copy the values in cells B4 and B5 on the Jobs sheet into two variables: myclient (string) and mydate (date).

(iii) Loop through all of the rows in the Job Archive table and for each row where the client in column A is equal to myclient and the date in column B is greater than mydate, copy the remaining contents of the row (columns B-H) into the next empty row in the Invoice table. To do this, you will need and IF statement inside your loop.

(iv) After the table is complete, make the Invoice sheet the active sheet (i.e., bring it to the front).

Hints

1) For your loop you will want to have two variables that work as row counters. One variable (jobrow for example) will be your main looping variable and will be used to keep track of what row you are looking at in the job list. You will initialize this to the first row in the table and increment it at the end of the loop and keep looping until you encounter an empty row (based on the contents of column 1). You will have another variable (invoicerow for example) that will keep track of the next available row in the invoice table. Initialize it to 9 and then increment it only after you copy a job to the invoice table. That way it will be pointing to the next available row for the next time you find a job to copy over.

2) When you are copying jobs over to the invoice table, you don't need to use copy-paste; you can just write the contents of the cells one at a time using assignment statements. For example:

Sheets("Invoice").Cells(invoicerow, 1) = Sheets("Jobs").Cells(jobrow, 2)
Sheets("Invoice").Cells(invoicerow, 2) = Sheets("Jobs").Cells(jobrow, 3)
etc.

Note that you need to explicitly reference the sheet that each cell is associated with since you are working with two separate sheets.

To help you see when you are on the right track, this is what the Invoice sheet should look like after you click on the Build Invoice button for Franklin & Associates. Note that the jobs with Franklin & Associates prior to 9/10/2007 (see top figure above) are not included in this list.

Submittal Instructions:

1. Be sure to save the changes to your spreadsheet.

2. Upload your spreadsheet via Learning Suite.