Sub abc()
Dim wsBH As Worksheet, ws15301 As Worksheet
Dim arrBH As Variant, arr15301 As Variant, arrKQ As Variant
Dim dict As Object
Dim lastBH As Long, last15301 As Long
Dim i As Long, key As String
Set wsBH = Sheets("BH")
Set ws15301 = Sheets("15301")
Set dict = CreateObject("Scripting.Dictionary")
'--- Lay du lieu:
lastBH = wsBH.Cells(wsBH.Rows.Count, "B").End(xlUp).Row
arrBH = wsBH.Range("A2:J" & lastBH).Value ' Du lieu chua ket qua
last15301 = ws15301.Cells(ws15301.Rows.Count, "L").End(xlUp).Row
arr15301 = ws15301.Range("A2:N" & last15301).Value ' Du lieu chua dieu kien
ReDim arrKQ(1 To UBound(arr15301), 1 To 1) 'Ket qua
'--- Tao dictionary: key = dk1 & "|" & dk2 & "|" & dk3
' Giá tri = cot J (cot thu 10 trong BH)
For i = 1 To UBound(arrBH, 1)
key = CStr(arrBH(i, 2)) & "|" & CStr(arrBH(i, 1)) & "|" & CStr(arrBH(i, 8)) ' B, A, H
If Not dict.exists(key) Then
dict.Add key, arrBH(i, 10) ' Cot J la cot ket qua
End If
Next i
'--- Tim va dien ket qua
For i = 1 To UBound(arr15301, 1)
key = CStr(arr15301(i, 12)) & "|" & CStr(arr15301(i, 14)) & "|" & CStr(arr15301(i, 3)) ' L, N, C
If dict.exists(key) Then
arrKQ(i, 1) = dict(key)
Else
arrKQ(i, 1) = CVErr(xlErrNA)
End If
Next i
'--- Ghi ket qua ra sheet
ws15301.Range("F2").Resize(UBound(arrKQ), 1).Value = arrKQ
MsgBox "Hoàn thành, da xu ly " & last15301 - 1 & " dong.", vbInformation
End Sub
