Option Explicit
Sub testCalcInventory()
Dim dic As Object, sheet As Worksheet
Dim inventory As Variant, scan As Variant, result As Variant, Key As Variant
Dim sPart As String, sLot As String, sTockNo As String, sTockNoTo As String, str As String, sTockNoSearch As String
Dim i As Long, j As Long, r As Long, u As Long, k As Long
Dim quantity As Double
Dim c As Integer, style As String
Const sDELIM As String = "|"
Set sheet = ThisWorkbook.ActiveSheet
inventory = sheet.Range("A3:E6").Value
scan = sheet.Range("G3:O29").Value
sTockNoSearch = "*KHO_004*"
If IsArray(inventory) Then r = UBound(inventory, 1)
If IsArray(scan) Then r = r + UBound(scan, 1)
If (r = 0) Then
Exit Sub
End If
If Not IsArray(scan) Then
GoTo Iventory_
End If
Scaner_:
Set dic = CreateObject("Scripting.Dictionary")
c = 8: ReDim result(1 To r * 2, 1 To c)
For i = LBound(scan, 1) To UBound(scan, 1)
style = scan(i, 1)
sPart = scan(i, 2)
sTockNoTo = scan(i, 3)
sTockNo = scan(i, 4)
sLot = scan(i, 7)
quantity = scan(i, 8)
str = Join(Array(sPart, sTockNoTo, sLot), sDELIM)
If Not dic.Exists(str) And (style = "MOV") Then
If UCase(sTockNoTo) Like UCase(sTockNoSearch) Then
k = k + 1
dic.Add str, k
result(k, 1) = k
result(k, 2) = sPart
result(k, 3) = sLot
result(k, 4) = sTockNoTo
End If
End If
Key = Join(Array(sPart, sTockNo, sLot), sDELIM)
If Not dic.Exists(Key) Then
If UCase(sTockNo) Like UCase(sTockNoSearch) Then
k = k + 1
dic.Add Key, k
result(k, 1) = k
result(k, 2) = sPart
result(k, 3) = sLot
result(k, 4) = sTockNo
End If
End If
u = dic.Item(str): r = dic.Item(Key)
Select Case style
Case "IN" 'NHAP KHO
If (r > 0) Then result(r, 6) = result(r, 6) + quantity
If (r > 0) Then result(r, 8) = result(r, 8) + quantity
Case "OUT" 'XUAT KHO
If (r > 0) Then result(r, 7) = result(r, 7) + quantity
If (r > 0) Then result(r, 8) = result(r, 8) - quantity
Case "MOV" 'CHUYEN KHO
If (u > 0) Then result(u, 6) = result(u, 6) + quantity
If (u > 0) Then result(u, 8) = result(u, 8) + quantity
If (r > 0) Then result(r, 7) = result(r, 7) + quantity
If (r > 0) Then result(r, 8) = result(r, 8) - quantity
End Select
Next i
If Not IsArray(inventory) Then
GoTo Result_
End If
Iventory_:
For i = LBound(inventory, 1) To UBound(inventory, 1)
sPart = inventory(i, 1)
sTockNo = inventory(i, 2)
sLot = inventory(i, 3)
quantity = inventory(i, 4)
Key = Join(Array(sPart, sTockNo, sLot), sDELIM)
str = Join(Array(sDELIM, sPart, sTockNo, sLot), sDELIM)
If UCase(sTockNo) Like UCase(sTockNoSearch) Then
If Not dic.Exists(Key) And (quantity > 0) Then
k = k + 1
dic.Add Key, k
result(k, 1) = k
result(k, 2) = sPart
result(k, 3) = sLot
result(k, 4) = sTockNo
result(k, 5) = quantity
result(k, 6) = quantity
Else
If Not dic.Exists(str) And (quantity > 0) Then
dic.Add str, quantity: r = dic.Item(Key)
result(r, 5) = quantity
result(r, 8) = result(r, 5) + result(r, 6) - result(r, 7)
End If
End If
End If
Next i
Result_:
If (k > 0) Then sheet.Range("P13").Resize(k, c).Value = result
End Sub
