do until loop need help with this problem

I am trying to make a macro for the following problem:


A1-Amount to Subtract to Zero
A2-45.20
A3-20.30
A4-10.00
A5-2.00

B1-Amounts to be subtracted from
B2-75.20
B3-5.50
B4-16.00
B5-13.20

C1-Amount Left
C2-0
C3-5.50
C4-13.70
C5-13.20



Alright this is my table and the heading say what each column should be. I want to take each amount in column A and subtract them from the max value in column b until every amount in column A reach 0. I don't want any negative numbers when a number reaches 0 in column B and I still have an amount left from column A I want to just carry that value and subtract it from the next highest value in B. I want each value in Column A to reach 0 when I am done.

Manually this is how i would do this problem

I would subtract 45.20 from 75.20 and then A1=0 and B1=30.00.

I would move on to A2 and subtract 20.30 from B1 which had 30.00 dollars left. A2=0 and B1=9.70.

I would move on to A3 and subtract 9.70 of A3 from B1 9.70 making B1 zero then subtract the remaining 30 cents to from B3=16.00 making B3=15.70.

I choose B3 instead of B2 because I want to take the largest values so the process is shorter.

A3 would equal 0 so I would move on to A4 and subtract 2.00 from B3=15.70 leaving B3 13.70

Column C would show the amounts remaining for column B to make sure we can tell how much is left after subtracting from the original amount.

I hope this makes sense. I've been trying different processes but I just can't grasp how to loop through both ranges.

try this

Please Login or Register  to view this content.

It worked fine for me. Here ill post the workbook.