Sub FIFO()
Dim aNhap(), aXuat(), Arr(), Res()
Dim srNhap&, srXuat&, i&, eRow&, Rk&
Dim Ngay As Date, MaSP$, iTest As Boolean
With Sheets("NHAP")
eRow = .Range("A" & Rows.Count).End(xlUp).Row
Arr = .Range("A4:H" & eRow).Value
.Range("A4:H" & eRow).Sort .[C4], 1, .[A4], , 1, Header:=xlNo
aNhap = .Range("A4:H" & eRow).Value
.Range("A4:H" & eRow).Value = Arr
End With
srNhap = UBound(aNhap)
With Sheets("XUAT")
eRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("I5:K" & eRow).ClearContents
.Range("L5").Value = 1
.Range("L5:L" & eRow).DataSeries
.Range("A5:L" & eRow).Sort .[C5], 1, .[A5], , 1, Header:=xlNo
aXuat = .Range("A5:F" & eRow).Value
End With
srXuat = UBound(aXuat)
ReDim Res(1 To srXuat, 1 To 3)
Rk = 1
For i = 1 To srXuat
MaSP = aXuat(i, 3)
Ngay = aXuat(i, 1)
Res(i, 3) = aXuat(i, 6)
iTest = False
For r = Rk To srNhap
If aNhap(r, 3) = MaSP Then
If aNhap(r, 1) > Ngay Then Exit For 'Ton kho khong "Am"
iTest = True
If aNhap(r, 6) > Res(i, 3) Then
aNhap(r, 6) = aNhap(r, 6) - Res(i, 3)
Res(i, 2) = Res(i, 2) + aNhap(r, 7) * Res(i, 3)
Res(i, 3) = 0
Exit For
ElseIf aNhap(r, 6) > 0 Then
Res(i, 2) = Res(i, 2) + aNhap(r, 6) * aNhap(r, 7)
Res(i, 3) = Res(i, 3) - aNhap(r, 6)
aNhap(r, 6) = 0
Rk = r
If Res(i, 3) = 0 Then Exit For
End If
Else
If iTest = True Then Exit For
End If
Next r
Next i
For i = 1 To srXuat
If Res(i, 2) > 0 Then Res(i, 1) = Round(Res(i, 2) / (aXuat(i, 6) - Res(i, 3)), 2)
If Res(i, 3) = 0 Then Res(i, 3) = Empty
Next i
With Sheets("XUAT")
.Range("I5").Resize(srXuat, 3) = Res 'Them cot so luong chua xuat duoc
'.Range("I5").Resize(srXuat, 2) = Res
.Range("A5:L" & eRow).Sort .[L5], 1, Header:=xlNo
.Range("L5:L" & eRow).ClearContents
End With
End Sub