Sub PhanBo_FIFO_CungSheet_Final_V2()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
' --- Cấu hình ---
Dim StartRow As Long
StartRow = 4 ' <<< DÒNG BẮT ĐẦU DỮ LIỆU (tùy chỉnh ở đây) >>>
Dim bResetF As Boolean
bResetF = True ' True = reset cột F = cột E trước khi chạy
' --- Biến chính ---
Dim ws As Worksheet
Dim lastRowNhap As Long, lastRowXuat As Long
Dim rN As Long, rX As Long
Dim maHang As String
Dim needQty As Double
Dim remainQty As Double
Dim takeQty As Double
Dim phieuID As String
Dim resultText As String
Set ws = ActiveSheet
' --- xác định dòng cuối ---
lastRowNhap = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' cột D = Mã hàng nhập
lastRowXuat = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row ' cột J = Mã hàng xuất
' --- (Tuỳ chọn) Reset cột F bằng cột E ---
If bResetF Then
For rN = StartRow To lastRowNhap
If IsNumeric(ws.Cells(rN, "E").Value) Then
ws.Cells(rN, "F").Value = CDbl(ws.Cells(rN, "E").Value)
Else
ws.Cells(rN, "F").Value = 0
End If
Next rN
Else
For rN = StartRow To lastRowNhap
If Not IsNumeric(ws.Cells(rN, "F").Value) Then ws.Cells(rN, "F").Value = 0
Next rN
End If
' --- Xóa nội dung cũ ở cột L ---
If lastRowXuat >= StartRow Then ws.Range("L" & StartRow & ":L" & lastRowXuat).ClearContents
' --- Phân bổ FIFO ---
For rX = StartRow To lastRowXuat
maHang = Trim(CStr(ws.Cells(rX, "J").Value))
If maHang = "" Then
ws.Cells(rX, "L").Value = ""
GoTo NextXRow
End If
If IsNumeric(ws.Cells(rX, "K").Value) Then
needQty = CDbl(ws.Cells(rX, "K").Value)
Else
needQty = 0
End If
resultText = ""
If needQty > 0 Then
For rN = StartRow To lastRowNhap
If needQty <= 0 Then Exit For
If Trim(CStr(ws.Cells(rN, "D").Value)) = maHang Then
If IsNumeric(ws.Cells(rN, "F").Value) Then
remainQty = CDbl(ws.Cells(rN, "F").Value)
Else
remainQty = 0
End If
If remainQty > 0 Then
If remainQty >= needQty Then
takeQty = needQty
remainQty = remainQty - takeQty
needQty = 0
Else
takeQty = remainQty
needQty = needQty - remainQty
remainQty = 0
End If
ws.Cells(rN, "F").Value = remainQty
phieuID = Trim(CStr(ws.Cells(rN, "B").Value))
If phieuID = "" Then phieuID = "?"
If resultText = "" Then
resultText = phieuID & "=" & FormatNumber(takeQty, 0, vbFalse, vbFalse, vbFalse)
Else
resultText = resultText & ", " & phieuID & "=" & FormatNumber(takeQty, 0, vbFalse, vbFalse, vbFalse)
End If
End If
End If
Next rN
End If
' --- Ghi kết quả ---
If resultText = "" Then
If needQty > 0 Then
ws.Cells(rX, "L").Value = "Không có mã hàng/Thiếu " & needQty
Else
ws.Cells(rX, "L").Value = ""
End If
Else
If needQty > 0 Then
ws.Cells(rX, "L").Value = resultText & " (Thiếu " & needQty & ")"
Else
ws.Cells(rX, "L").Value = resultText
End If
End If
NextXRow:
Next rX
MsgBox "Hoàn tất phân bổ FIFO từ dòng " & StartRow & "!", vbInformation
CleanExit:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox "Lỗi: " & Err.Number & " - " & Err.Description, vbCritical
Resume CleanExit
End Sub
