How to Apply Payments to Aged Receivables Automatically using Excel VBA

[DocAElstein]

DocElstein, I think the negative numbers in the grid represent the overpayment of the debt in that column. I'm guessing that an amount has been paid off, destined for a specific age of the debt and that amount was more than necessary to pay off that part and what we see is the overpayment part only. Consequently I think those values need to be made 0, but the amount of overpayment needs to be distributed among the remaining aged debts, starting with the oldest.

I've written a small bit of vba code for the OP to confirm it's right, before I refine it.....
.

Hi Pascal
Thanks for the clarification, I think I had finally just about twigged to that, so that was / is my final conclusion, similar to yours, something like this was finally in my head:
At some point all the columns E to I are either empty of black showing debt. Then someone or something comes along and "pays" somehow money into a column which will reduce that debt in that column. ( That is done manually,and according to the last info from hhap,- a Lady does that manually, and If he or it pays too much, she puts the difference in, in –ve, and then the column turns red and – indicating a credit). That is where we come in.
So at that point a VBA code is wanted, – we use the ( total ) credit to reduce as much of the remaining debts in columns E to I that it can, starting from the right.
That seems to be similar what you are saying
I have not considered the case of if the credit is more than the total summed debts from all columns. I think perhaps your code might be allowing for that possibility, at first glance I am not sure what you are doing, but as ever that is me, being slower than everyone else to get the point, Lol. It seems to get the correct results as far as I can tell.

( P.S. we have some similar thoughts, Lol, I always copy the original OP worksheet, and often get in a muddle with the two at some point , I need to think of doing something like you did with a macro always to tidy my workings up a bit)

Alan

[p45cal]

I have not considered the case of if the credit is more than the total summed debts from all columns. I think perhaps your code might be allowing for that possibility

I did consider it and coded something for it but didn't include it in my attachment.
It was one line of code which adds a comment in column L:

  Next clm
  If PaymentToApply > 0 Then Cells(rw.Row, "L").Value = "Unallocated amount: " & PaymentToApply '<<<<<<added line
Next rw