Macro to limit total sum over multiple rows

Hi All,

I hope everyone is having a good run up to Christmas!

I would like to create a script to replicate the steps below so that it does not take me the 3-4 hours it currently does each week to figure out how many hours everyone has actually completed. This is all part of a weekly financial tracker I manage. In the attached spreadsheet I have multiple rows for bookings by individuals in my team.

What I need to do is limit the total sum of bookings per week to 40 hours as anything beyond 40 needs a different overtime rate.

Current process:

1. I sum each row
2. For any row over 40:
- I reduce the total value per person per week to 40 by creating a new negative entry (HIGHLIGHTED IN YELLOW)
- Negative entry is a null row (copying a persons name and the end of week date) with a negative quantity value that equates the total sum to 40.
3. I separately record how many hours over 40 a person has completed.

Any ideas, suggestions or working macros would be very much appreciated.

Thank you all and merry Christmas!

Hi Mumps1,


I am getting a "Run-time error '1004': no cells were found, which seems like it is being caused by the step [For Each Rng In Range("G2:G" & LastRow).SpecialCells(xlCellTypeVisible)]


Thank you for your response!

Hi Mumps1,

Yes karedogs, solution works perfectly! I have a minor edit I would love to integrate but even so that's cosmetic.

I am not too sure why your solution is not working - probably my incompetence but again thank you for all the help!

Brilliant karedog!

Rest assured there will be many people who will feel the blessings from your kindness.

I will make sure to come back and give you a reputation for this follow up answer as soon as I spread the cheer to others. Thank you!