Allocating Receipt amount with invoices based on fifo method

[amandeep08]

Hi,

In my workbook I have 2 sheets - 1st sheet has the case and 2nd has the solution of how the case should look like after running the macro.

In sheet - "CASE" I have 2 sets of data namely Data 1 - Invoices & Data 2 - Receipts.

I have a code which would allocate all the receipts with the Invoices serial no. wise.

If a single receipt is not sufficient to adjust a Invoice, a row is inserted and in that row the remaining balance of invoice is adjusted against next receipt.

The Macro is working fine if i am applying to one customer but how to apply on multiple customers in one go.

Sample (1).xlsm
	A	B	C	D	E	F	G	H	I
1	Invoice						Receipts
2	S.No.	Customer	Invoice	Receipt no.	Receipt adjusted		S.no	Receipt	Customer
3	1	A	2865				1	14325	A
4	2	A	4298				2	15757	A
5	3	A	14125				3	120	A
6	4	A	950				4	1433	A
7							5	1433	A
8							6	1433	A
Case

Below is the code:

Sub Test()
Dim rng As Range, a, b, c(1 To 100000, 1 To 5), i As Long, j As Long, pb As Long, pc As Long, v3
With Sheets("Case ")
Set rng = .Range("A1").CurrentRegion
a = rng.Offset(2).Resize(, 3).Value
b = .Range("G3:H" & .Cells(.Rows.Count, "G").End(xlUp).Row).Value
pb = 1
For i = 1 To UBound(a, 1)
v3 = a(i, 3)
For j = pb To UBound(b, 1)
pc = pc + 1
c(pc, 1) = a(i, 1): c(pc, 2) = a(i, 2): c(pc, 3) = v3: c(pc, 4) = b(j, 1)
If a(i, 3) < b(j, 2) Then
c(pc, 5) = a(i, 3)
b(j, 2) = b(j, 2) - a(i, 3)
Exit For
ElseIf a(i, 3) = b(j, 2) Then
c(pc, 5) = a(i, 3)
pb = pb + 1
Exit For
Else
c(pc, 5) = b(j, 2)
a(i, 3) = a(i, 3) - b(j, 2)
pb = pb + 1
End If
Next j
If pb > UBound(b, 1) Then
pc = pc + 1
c(pc, 1) = a(i, 1): c(pc, 2) = a(i, 2): c(pc, 3) = v3
End If
Next i
End With
With Sheets.Add(after:=Sheets(Sheets.Count))
rng.Rows("1:2").Copy .Range("A1")
.Range("A3").Resize(pc, UBound(c, 2)).Value = c
With .Range("A1").CurrentRegion
.Borders.Weight = xlThin
.EntireColumn.AutoFit
End With
End With
End Sub



Thanks in advance!

 

[amandeep08]

No one has revert yet. Seems requirement is little complicated