Option Explicit '// Them dong nay de kiem soat cac bien co duoc din nghia day hay khong?
Sub Capnhat_Thoigian() '// Ten thu tuc
'//Khai bao bien
Dim dic As Object, arrCD(), arrData(), rData As Range, sKey As String, r As Long, i As Long
With ThisWorkbook.Worksheets("DATA CD") '// Lam viec voi Sheet "DATA CD"
r = .Cells(.Rows.Count, "C").End(xlUp).Row '// Xac dinh dong cuoi trong co C
If r < 2 Then Exit Sub '// Thoat neu khong co du lieu
'// Gan du lieu vao arrCD tu C2 den dong cuoi cua cot C quet sang phai 7 cot
arrCD = .Range("C2:C" & r).Resize(, 7).Value2
End With '//Ket thuc lam viec voi Sheet "DATA CD"
With ThisWorkbook.Worksheets("DATA") '// Lam viec voi Sheet "DATA"
r = .Cells(.Rows.Count, "B").End(xlUp).Row '// Xac dinh dong cuoi trong co B
If r < 3 Then Exit Sub '// Thoat neu khong co du lieu
'// Gan du lieu vao arrData tu B3 den dong cuoi cua cot B quet sang phai 5 cot
arrData = .Range("B3:F" & r).Resize(, 5).Value2
Set rData = .Range("B3") 'Gan B3 vao bien rData cho gon de xuong duoi khong phai viet lai ten sheet
End With '//Ket thuc lam viec voi Sheet "DATA"
'//Khoi tao DIC
Set dic = CreateObject("Scripting.Dictionary")
'// Bat dau duyet tu vi tri dau tien den vi tri cuoi cung theo chieu thu 1 mang arrData
'// tu tren xuong duoi tuong ung tren bang tinh tu B3:B3157 sheet Data
For i = LBound(arrData, 1) To UBound(arrData, 1)
'//Tim kiem theo 3 tieu chi : so oder / ma hang / tien do
'sKey = arrData(i, 1) & "|" & arrData(i, 2) & arrData(i, 4)
sKey = arrData(i, 1) '//Tim kiem theo 1 tieu chi : so oder
'//Neu trong DIC chua ton tai sKey,gan sKey vao DIC va ghi nho vi tri thu i
If Not dic.Exists(sKey) Then dic.Add sKey, i
Next i '// Ket thuc vong lap (dong cuoi cung)
'// Bat dau duyet tu vi tri dau tien den vi tri cuoi cung theo chieu thu 1 mang arrData
'// tu tren xuong duoi tuong ung tren bang tinh tu C2:C4233 sheet "DATA CD"
For i = LBound(arrCD, 1) To UBound(arrCD, 1)
'//Tim kiem theo 3 tieu chi : so oder / ma hang / tien do
'//sKey = arrCD(i, 1) & "|" & arrCD(i, 2) & "|" & arrCD(i, 4)
sKey = arrCD(i, 1) '//Tim kiem theo 1 tieu chi : so oder
If dic.Exists(sKey) Then '// Kiem tra xem sKey co ton tai trong DIC khong
'/Cung giong voi viec kiem tra so oder trong "DATA CD" co ton tai trong sheet "DATA" khong
r = dic.Item(sKey) '// Neu ton tai tra ve so dong so Oder tim kiem
'// So sanh thoi gian hoan thanh trong "DATA CD" voi gia tri arrCD(i, 6): cot thu 6(CotH, tu C den H), dong r tim duoc o tren
'// so voi thoi gian hoan thanh trong sheet "DATA" voi gia tri arrData(r, 5): cot thu 5(CotF, tu B den F), dong r tim duoc o tren
If arrCD(i, 6) > arrData(r, 5) Then '// Neu lon hon
arrData(r, 5) = arrCD(i, 6) '// Cap nhat thoi gian cua sheet "DATA CD"
arrData(r, 4) = arrCD(i, 4) '// Cap nhat tien do cua sheet "DATA CD"
End If '// Ket thuc If cua neu lon hon
End If '// Ket thuc If cua neu tim duoc so oder
Next i '// Ket thuc vong lap (dong cuoi cung)
r = UBound(arrData, 1) '// So dong du lieu sheet DATA khong bao gom dong tieu de
i = UBound(arrData, 2) '// So cot du lieu sheet DATA khong tu B den F
rData.Resize(r, 2).NumberFormat = "@" '// Dinh dang cot B,C dang Text tranh sai so oder & ma hang
rData.Resize(r, i).Value = arrData '// Ghi ket qua xuong bang tinh
End Sub
