Settle credit notes by VBA

[omey]

Dear vba experts

I'm trying to create a macro and don't know how to proceed.

I need a macro that searches through column F(amount). If it finds a negative amount(a credit note), it should search through column A(client ID), look for the same client and deduct the amount from the oldest invoice of this client. Then it should delete the whole line of the credit note.
If it doesn't find the same client, it should not adjust anything.

If the negative amount is a cancellation(all records with the invoice number 9999999), it should not adjust anything.

If the amount of the oldest invoice is smaller than the credit note, it should deduct down to 0 and deduct the rest of the credit note from the second oldest invoice.

I hope my explanation is good enough. I have attached a sample file.

I would be grateful if someone could help me further.

[xladept]

Duplicate Post Deleted

[xladept]

Hi Omey,

This is what I've got so far but were there any credit-debit matches in your sample?

Please Login or Register  to view this content.

[omey]

hi,

Thanks a lot for your help.

Could you please explain me the steps you do in the macro?

When I run it, it takes hours and never stops..

[omey]

hi,

Thanks a lot for your help.

Could you please explain me the steps you do in the macro?

When I run it, it takes hours and never stops..