Printable View
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.
What does "summarise the costs" mean? Add them up to get a total?
Sorry, I should explained it better.
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)
Hmm, smells like a class assignment!! :D :lol:
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.
CheersCode:=SUM(VLOOKUP($B$2,Rates,2,FALSE)*B3)
-EM.
Well past class assignments but still not a VBA guru.
Worked out that it needs to be Application.VLookup(
The number of employees is variable though so I can't use your advice.
I think that I just need to use for i = ... for j = ...
No drama... just hoping someone would show me something startling.
just sum the vlookups for each employee
I can't do it in the sheet unfortunately as I insert the hours in from individual timesheets using vba which would overwrite formulas.