Attached is what I want to do. I want to use vba to summarise the costs.
This might seem lazy of me but I can do this (not easily) but it would take around 30-40 lines.
I'm sure it can be done in 5 or 6 lines by setting two ranges and looking the data up. I just am not familiar with doing it.
I have a whole timesheet project that has so far been done very clumsily in my eyes. Be great if I could tidy the whole thing up by shrinking the code and making it work faster.
I need the total cost for each job. Employees have different salaries.
So in the sheet Job Number 1 would cost $415 (8 hours x $10/hour + 9 hours x $15/hour + 10 hours x $20/hour)
Why VBA? From the looks of this you're supposed to put a formula on the sheet.
The SUM formula does just that, sums values. You can use VLOOKUP to search for the correct rate and then multiply the correct value by it.
This sample does it for the first column, and I'll leave it up to you to add the next 2 columns to the SUM formula! BTW, in your uploaded workbook, the list of names and rates is a named range called "Rates", hence the appearance of "Rates" in the VLOOKUP below.
Code:
=SUM(VLOOKUP($B$2,Rates,2,FALSE)*B3)
Cheers
-EM.
--- REMEMBER: If your issue is resolved, use the Thread Tools menu to set it as such, and be sure to rate the posts that help you the most!
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
[RESOLVED] Ranges and lookups using vba.
Reply With Quote
BTW, in your uploaded workbook, the list of names and rates is a named range called "Rates", hence the appearance of "Rates" in the VLOOKUP below.
