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

Hi,

I am trying to write a macro for getting payment date against each invoice.

Eg:
Invoice Date Inv Value Customer Code Inv_No
1 Jan 15 100,0000 1 1

Payment Detials of same customer
payment Date Amt Customer code Paymentref
2 Jan 15 950000 1 1
5 Jan 15 5000 1 2

In this case i want against first invoice clearing date as 5 jan 2015(since that is the date when the entire amount is cleared.)

I have two sheets one sheet has Invoice details for all customers and other sheet has payment details for all customers.

Logic: I want VBA to read each payment line item and then compare whether it belongs to the same customer and if it is then it will compare whether payment value is greater than invoice value if it is TRUE then it will allocate current payment date as clearing date to the invoice selected. Else it will calculate inv_bal and looping will be done to achieve clearing date of each line.

Here is the code

Sub fifoadjustment()
Dim i, j As Integer
Dim payment_bal, invoice_bal As Integer

payment_bal = 0
invoice_bal = 0

For i = 1 To 22
For j = 1 To 5

payment_bal = Sheets(2).Range("i" & j + 1).Value
invoice_bal = Sheets(1).Range("h" & i + 1).Value

If payment_bal > invoice_bal Then
       Sheets(1).Range("I" & i + 1) = Sheets(2).Range("E" & j + 1)
    payment_bal = payment_bal - invoice_bal
    
    Else
    invoice_bal = invoice_bal - payment_bal
    
    End If
    
    Next j
    
    Next i
  
End Sub

Please help me out, it is not working and throwing Run time error '6' Overflow.


Regards
Prem