Option Explicit
Public Sub thuong_ban_hang()
Dim data, NV, KQ As Variant, R_NV As Range, i, j, k, tong_so_hang, tong_so_hang_thuong As Long, dic As Object, r
ReDim KQ(1 To 6000, 1 To 8)
With Sheets("Nhan_vien")
Set R_NV = .Range(.[c3], .[c60000].End(3))
NV = R_NV.Resize(, 2).Value
End With
With Sheets("DATA")
data = Range(.[ah6], .[ak60000].End(3)).Value
End With
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(data)
If data(i, 2) <> "" Then
If UCase(Left(data(i, 4), 1)) = "C" Then
If Not dic.exists(data(i, 2)) Then
k = k + 1
dic.Add data(i, 2), k
KQ(k, 1) = k
KQ(k, 2) = data(i, 2)
r = Application.Match(data(i, 2), R_NV, 0)
If TypeName(r) <> "Error" Then
KQ(k, 3) = NV(r, 2)
Else
KQ(k, 3) = "Ko co nhan vien nay trong danh sach"
End If
KQ(k, 4) = 1: tong_so_hang = tong_so_hang + 1
KQ(k, 5) = 1: tong_so_hang_thuong = tong_so_hang_thuong + 1
KQ(k, 7) = "=RC[-2]*RC[-1]"
Else
j = dic.Item(data(i, 2))
KQ(j, 4) = KQ(j, 4) + 1: tong_so_hang = tong_so_hang + 1
If UCase(Left(data(i, 4), 1)) = "C" Then KQ(k, 5) = KQ(j, 5) + 1: tong_so_hang_thuong = tong_so_hang_thuong + 1
End If
End If
End If
Next i
k = k + 1
KQ(k, 2) = "Tong"
KQ(k, 4) = tong_so_hang
KQ(k, 5) = tong_so_hang_thuong
KQ(k, 7) = "=SUM(R[-" & k - 1 & "]C:R[-1]C)"
With Sheets("Thuong_ban_hang")
.[a11:H6000].Clear
.[a11].Resize(k, 8) = KQ
.[a11].Resize(k, 8).Borders.Weight = xlThin
End With
End Sub
