VBA code to apply payments to invoices

    Hi,

    I have a data set and I would like to apply payments to balances,


    Basically applying the opening balance (Negative) to the bill starting with oldest to newest.

    Attached is a sample

    Sheet 1 is the transactions

    Sheet 2 is opening payments

    Sheet 3 is expected results


    any help is greatly appreciated

    Hi Jason,


    I cannot see how the sheets relate, ID 100000 for example disappears in sheet 3, ID 1040000 repeats on sheet 3 except it has brackets around the negative value and no account is taken of the 1459 that is present on sheet 2. ID 2309D is only on sheet 2 and nowhere else.


    Essentially I do not see what is supposed to be occurring.

    Hey,


    Soo, sheet one is invoices so customer 100000 has $92, sheet 2 is the opening balance for each customer, in this case $-92 , as a result i want to apply the -92 to the 3 invoices in tab one, resulting in noting from sheet one being transferred to sheet 3. For the second customer 0000273518 there opening balance was 0 so i want all related transactions to be transferred over. for the 3rd 0001040000 they had a positive balance i want all the transactions transferred over and so on and so on


    i hope its possible, and thanks for any help.

    Why have 3 sheets? You should use one sheet to record all invoice data on Invoice Number,Invoice Date, Amount,etc including one for Date Paid. Then you build reports based on that.


    Your present design, especially having empty columns between the data is no good for working with data in Excel.


    Data in Excel

    Quote from royUK

    Why have 3 sheets? You should use one sheet to record all invoice data on Invoice Number,Invoice Date, Amount,etc including one for Date Paid. Then you build reports based on that.


    Your present design, especially having empty columns between the data is no good for working with data in Excel.


    Data in Excel

    Hey,


    I'm trying to process the data on sheet on with the data on sheet two and sheet 3 is the output

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login