FIFO Based Receipt Adjustment To determine date when particular invoice is finally cleared

Originally Posted by spremkishan

Elloit,

Logic provided is not working as desired.

I have sorted file of invoice and payments based on customer and then oldest to newest.
Now, logic should be in such a way that it will read every payment line, and then it should read first invoice line if customer in payment line is matching with customer as per invoice file then it should compare whether payment amount is greater than zero in that case it should allot current payment line date as clearing date in invoice file. Now it should calculate payment balance and then proceed to next invoice
In case payment amt is less than invoice amt. it should calculate invoice balance and proceed to next payment.

Thanks for helping!!.

Regards
Prem.

upload sample file, with new code this should work.

Elloit,

Here is the Sample File and code below

Sub fifoadjustment()
Dim i, j As Long
Dim Customer As String
Dim payment_bal, invoice_bal, invoice_amt As Long


Sheets("Invoice").Range("L2:L50000").ClearContents


payment_bal = 0
invoice_bal = 0
invoice_amt = 0

For i = 2 To Sheets("Payments").Range("A70000").End(xlUp).Row
If payment_bal = 0 Then
payment_bal = Sheets(2).Range("G" & i).Value
Else
payment_bal = payment_bal
End If

Customer = Sheets(2).Range("A" & i).Value

For j = 2 To Sheets("Invoice").Range("A70000").End(xlUp).Row
    
    If Sheets(1).Range("A" & j) = Customer Then
        If invoice_bal = 0 Then
        invoice_bal = Sheets(1).Range("G" & j).Value
        Else
        invoice_bal = invoice_bal
        End If
    
        If payment_bal >= invoice_bal Then
           Sheets(1).Range("L" & j) = Sheets(2).Range("L" & i)
            payment_bal = payment_bal - invoice_bal
            invoice_bal = 0
            Sheets(2).Range("M" & j).Value = payment_bal
       'GoTo NextInv
        Else
            invoice_bal = invoice_bal - payment_bal
            payment_bal = 0
            GoTo NextPayment
            
        End If
    End If 'need to capture before end if what if customer is not matching in that case it should go to next invoice and next payment
            'hence i have not given any more conditions assuming system will anyway do the same.
               
NextInv:
Next j


'Next i should be read only if payment_bal=0 so that iteration will happen for all invoices and allocation will be done
If payment_bal = 0 Then GoTo NextPayment

NextPayment:
Next i

End Sub