Calculations with VBA: Sum Product and Rate Lookup

Hello everyone,
I hope you're all doing well today. I have a straightforward calculation that I intend to execute using VBA instead of direct sheet formulas. I'm specifically focusing on the yellow highlighted columns for this task.

The given sheet already calculates the sum of working hours for each employee in every row.

Now, I need to calculate the wage for each row, as indicated in column I. The formula in column I is simply (F2 * 24) * 0.54. Note that 24 is a constant, while the number 0.54 is variable based on the individual's hourly rate, as shown in table hour_rate (in Sheet2).

So, I guess it is a VLOOKUP code that would retrieve the value of 0.54 from Sheet2. Hence, when this is achieved, I intend to perform a sum product operation on the values in column I, and the sum should consider only rows where the person is the same and has the same PRO. number simultaneously. These sum product values are calculated in column J.

I've populated a few rows to illustrate the data. Please find below the VBA code for which a vba code may be added.

Please Login or Register  to view this content.

I appreciate your assistance in implementing these calculations using VBA.

Thank you very much!

Attached is my whole file as for your sole convenience.

Hey John,

Your solution is working perfectly. However, I anticipate a need to update the hourly rates in Sheet2. When I make these changes, I'd like to preserve the calculations based on the previous rates and only apply the new rates to future calculations. How can we manage this effectively?

Hi John,
I've understood your suggestion. In this scenario, I formulate a new table in Sheet2.
I assume in my case that when the start date matches the effective date that i enter in cell F2 on Sheet2, that specific hourly rate should be considered.
For any other cases, your previous code should handle it as before.

To clarify, if there is no effective date specified, or if the start date is before the effective date, the original hourly rate remains effective.
Only when there is a matching start date with the specified effective date should the new rate come into play.
I appreciate your guidance on how to incorporate these adjustments.
Thank you.

Ah, you're right..here is the new updated workbook

Hi John, I hope you're doing well. I apologize for the delay in responding. I've discussed your proposed effective date solution with my teammates, and they've decided to implement it. In the event that the effective date expires, the plan is to transition to another sheet. Thank you so much for your code – your solution has significantly streamlined our processes. Your assistance is greatly appreciated.