Sub BaocaoNXT()
Dim lastRow As Long, r As Long, tungay As Long, denngay As Long, tonKK As Long, count As Long, dvt As String, key, data(), item(), ton As Object, baocao As Object
' truoc het xoa du lieu cu
With ThisWorkbook.Worksheets("BC_NXT")
lastRow = .Cells(Rows.count, "A").End(xlUp).Row
If lastRow > 9 Then .Range("A10:H" & lastRow).ClearContents
tungay = .Range("B4").Value
denngay = .Range("B5").Value
End With
' Tinh ton kho cho tung MaH o ngay TON_KHO!H3
With ThisWorkbook.Worksheets("TON_KHO")
lastRow = .Cells(Rows.count, "A").End(xlUp).Row
If lastRow < 7 Then Exit Sub
' lay du lieu ton vao mang
data = .Range("A7:J" & lastRow).Value
End With
' tu dien co MaH la KEY va mang (soluong, dvt) la ITEM
Set ton = CreateObject("Scripting.Dictionary")
' khong phan biet hoa va thuong khi kiem tra MaH
ton.comparemode = vbTextCompare
' tu dien baocao co MaH la KEY va mang item la ITEM. mang item = (trongkhoang, tenHH, MaKH, Ton, Tong Nhap, Tong Xuat, Dvt)
Set baocao = CreateObject("Scripting.Dictionary")
' khong phan biet hoa va thuong khi kiem tra MaH
baocao.comparemode = vbTextCompare
' duyet mang ton kho
For r = 1 To UBound(data)
If Not ton.exists(data(r, 1)) Then
ReDim item(1 To 2)
item(1) = data(r, 8)
item(2) = data(r, 10) ' dvt
ton.Add data(r, 1), item
Else
item = ton.item(data(r, 1))
' cong don so luong
item(1) = item(1) + data(r, 8)
' lam moi item
ton.item(data(r, 1)) = item
End If
Next r
' duyet sheet THE_KHO
With ThisWorkbook.Worksheets("THE_KHO")
lastRow = .Cells(Rows.count, "A").End(xlUp).Row
If lastRow < 10 Then Exit Sub
' lay du lieu ton vao mang
data = .Range("A10:K" & lastRow).Value
End With
' duyet mang the kho
For r = 1 To UBound(data)
If Not baocao.exists(data(r, 1)) Then
' neu co ton kiem ke (khong la mat hang moi) thi doc ra tonKK
If ton.exists(data(r, 1)) Then
item = ton.item(data(r, 1))
tonKK = item(1)
dvt = item(2)
Else
tonKK = 0
dvt = ""
End If
ReDim item(1 To 7)
item(2) = data(r, 2) ' TenH
item(3) = data(r, 3) ' MaKH
item(4) = tonKK ' ton hien hanh
item(7) = dvt ' dvt
' them muc voi Ma H vao tu dien baocao
baocao.Add data(r, 1), item
Else
item = baocao.item(data(r, 1))
End If
If data(r, 6) < tungay Then
' muc nam truoc khoang, cong don ton dau
item(4) = item(4) + data(r, 10) - data(r, 11)
ElseIf data(r, 6) <= denngay Then
' muc nam trong khoang
' danh dau "x" de biet la nam trong khoang
item(1) = "x"
' cong don tong Nhap
item(5) = item(5) + data(r, 10)
' cong don tong Xuat
item(6) = item(6) + data(r, 11)
End If
' lam moi item
baocao.item(data(r, 1)) = item
Next r
' mang ket qua
ReDim data(1 To baocao.count, 1 To 8)
For Each key In baocao.keys
item = baocao.item(key)
If item(1) = "x" Then
' muc nam trong khoang dang xet
count = count + 1
data(count, 1) = key ' MaH
data(count, 2) = item(2) ' TenH
data(count, 3) = item(3) ' MaKH
data(count, 4) = item(4) ' Ton dau
data(count, 5) = item(5) ' Tong Nhap
data(count, 6) = item(6) ' Tong Xuat
data(count, 7) = item(4) + item(5) - item(6) ' Ton kho
data(count, 8) = item(7) ' dvt
End If
Next key
ThisWorkbook.Worksheets("BC_NXT").Range("A10").Resize(count, 8).Value = data
Set ton = Nothing
Set baocao = Nothing
End Sub
, OT cũng đang học code nên khai báo các tham số theo mục đích sử dụng nên nó hơi kỳ cục hihi.
