SUMIF and LOOP till the last row

Hi

How would one go about creating a macro that would sum values in column G based on a condition in F ( a "SUMIF" basically) until the end of the range?

The condition would be so that the first blank cell in Column G (starting from column G9 ie Range("G9").End(xlDown).Offset(1,0) would contain the sum of everything above up until the word "Total" (or even the string of 5 characters from the left say "Total") appear in Column F - (the word Total appears a few times in column F, so it should detect the first applicable one)

The idea would be to have this repeated / looped until the end of the data range

Attached is an example and I hope what I have written makes sense

As for the worksheets:

"Final" is what it should look like (Blue Cells represent manual calculations)

"Working" Sheet is where I have it up to now

The sheet "Original" has a button that recreates everything up until where "Working" Sheet is

Sheet ("Tenant History Schedule") is scrap sheet

Thanks guys

Onemore question
Do you want to Sum up everything except for Totals and re-color them in blue color as well?

doesn't need to be blue, that was just there to highlight where the calculation fits

It should sum everything up to and including the value in Column G where the word "Total" appears in Column F (ie where the first 5 strings from the left are total) last if working from the bottom up

If you look at G47 in the final sheet, it sums everthing up from G46 to G30 (F30 is the word total)

Then the next cell that requires a calculation is G49, that sums everything from G48 up until where it recognises the first time "total" appears above it in column F thus it sums G48 to G47 (F47 has the word Total)

Similarly, G62 adds everything up from G61 to G58 (where F58 has the first 5 characters as Total)

Does that help?

hi and sorry for very! late response. I had to go yesterday, and couldn't post reply. Below code is what I came up with. Hope it will help you

Please Login or Register  to view this content.

bambino32, you have been a member long enough to know that:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.