Cumulative sum using Do Until ... Loop

[mati_WAR]

Hi,

I want to calculate sum of each amount from Input.xlsx file, depending on the aging calculate in column L.

For now I have this:

Please Login or Register  to view this content.

The problem is that I want to each sum (sum0, sum 30, etc.) contains the cumulative sum for each aging bucket. So later when I will want to refer to sum30 it will contains all amounts summed together.
For now each sum remembers only sum for each row one at a time.

What do you think about this?

[leelnich]

Hi mati_WAR, try this:

Please Login or Register  to view this content.

Since the tests ascend in value, there's no need to check the "Floor"; previous buckets will claim any smaller values.

[CK76]

Like leelnich I'd go with Select Case.

As well, instead of looping through cells directly. It's much faster to commit range to array and loop through array in memory.

Please Login or Register  to view this content.

[mati_WAR]

Thank you both for help :-)

In fact, Select Case makes much more sense in this case.

Regarding If method I have also updated my part of code to be more efficient:

Please Login or Register  to view this content.

[leelnich]

Mati, post #4 looks like post #1. Did you paste the right code? In any case (lol), if you are satisfied with the answer, please go to Thread Tools (up top) and mark your thread SOLVED. Thanks! - Lee

[mati_WAR]

Mati, post #4 looks like post #1. Did you paste the right code? In any case (lol), if you are satisfied with the answer, please go to Thread Tools (up top) and mark your thread SOLVED. Thanks! - Lee

Yeah, it's almost the same. I delete "WorksheetFunction.sum" from the code and also "End if" was in incorrect place, because it caused freezing of the macro :-D

[leelnich]

Got it, thanks for the rep!