How to Apply Payments to Aged Receivables Automatically using Excel VBA

[p45cal]

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.
It's the blah macro. It creates a copy of the worksheet and works on that.
At the moment it produces the same results as the manual solution.

Comments in the code.

[hhap]

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.
It's the blah macro. It creates a copy of the worksheet and works on that.
At the moment it produces the same results as the manual solution.

Comments in the code.

I think DocElstein code removes the negative numbers after it has applied the payments to the remaining aged debts starting with the oldest. As you stated your approach will give the same result as the manual. During payment by customers, the cashier is supposed to manually distribute the payment to the aged debts starting with the oldest. However the customers are a lot so she just post it as a credit for the customer and therefore the payment is not applied to the aged debts at all.