Sum numbers under a threshold

[dislexic37]

Hi Everyone,

I'm trying to write a macro in VBA which sums up a list of numbers up to but not over a limit (10,000.00) starting from the top and then return the sum at the bottom of the column. I'd also like it to return the sum of the related numbers in the next column as well. I think this is best expressed in my example below. Column A is the column which I'm referring to for the 10,000 limit.

Macro example.PNG

The 10,000 limit is breached in column A at row 13 where the total is 10,431.15 so I need to return the sum of the entries before this point (i.e. sum of the numbers in A1 to A12 in this example) which would be 9424.54. At the bottom of column B the corresponding entries pre the 10,000 breach in column A are 282.83 (i.e. sum of the numbers in B1 to B12).

I'm fairly new to VBA so any help or guidance would be greatly appreciated.

[Arkadi]

dislexic37,

Is the idea that the summed group also gets highlighted as per your image? Is the image the actual format? (i.e. columns A and B and no headers?)

[dislexic37]

Hi Arkadi,

No the highlighting was just to help with the explanation of which numbers I was trying to get to sum (i.e. when the 10,000 limit was reached). There will probably be a header in row 1 but I thought that if I solved the problem without headers then I'd be able to shift the result down one when I added a header.

[Arkadi]

Ok, this will work i think, but you won't know up to where the numbers were summed? Check this:

Please Login or Register  to view this content.

[stnkynts]

Here is my go at it.

Please Login or Register  to view this content.

[dislexic37]

Hi Arkadi,

The sum at the bottom of column A is spot on. At the bottom of column B it appears to be adding A1 to A12 and then adding the value in A12 to give 11,381.94 rather than the sum of B1 to B12.

[dislexic37]

Stnkynts,

Your solution gives 9,805 as the sum for column A and then adds up the whole of column B.

[Arkadi]

dislexic my bad... here's the fix:

Please Login or Register  to view this content.

[Arkadi]

dislexic my bad... here's the fix:

Please Login or Register  to view this content.

[berlan]

Another alternative,

Please Login or Register  to view this content.

Cheers, berlan

[stnkynts]

Stnkynts,

Your solution gives 9,805 as the sum for column A and then adds up the whole of column B.

It doesn't on my test workbook.

[dislexic37]

Thank you everyone for you help, problem solved. Cheers.