Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B5:D" & Rows.Count)) Is Nothing Then
Static Dic As Object
Dim sArr(), Arr(), Lr&, I&, ShowMsg As Boolean, iKey$, Str$
Const iMax# = 1000000#, N$ = vbNullString
If Dic Is Nothing Then Set Dic = CreateObject("Scripting.Dictionary") Else Dic.RemoveAll
Lr = Cells(Rows.Count, "B").End(xlUp).Row
sArr = Range("B5:D" & Lr).Value2
Arr = Range(Cells(Target.Row, "B"), Cells(Target.Rows(Target.Rows.Count).Row, "D")).Value2
For I = 1 To UBound(sArr)
iKey = sArr(I, 1) & "|" & sArr(I, 2)
Dic(iKey) = Dic(iKey) + sArr(I, 3)
Next
For I = UBound(Arr) To 1 Step -1
iKey = Arr(I, 1) & "|" & Arr(I, 2)
If Dic(iKey) > iMax Then
If InStr(1, Str, Arr(I, 1), 1) = 0 Then
Str = Str & vbNewLine & "NhanVien: " & Arr(I, 1)
End If
Dic(iKey) = Dic(iKey) - Arr(I, 3)
Arr(I, 1) = N: Arr(I, 2) = N: Arr(I, 3) = N
End If
If Arr(I, 1) <> N And Arr(I, 2) <> N And Arr(I, 3) <> N Then ShowMsg = True
Next
Cells(Target.Row, "B").Resize(UBound(Arr), UBound(Arr, 2)) = Arr
If ShowMsg Or Len(Str) Then
Str = "Cac ma sau khong thoa man, da xoa: " & Str
MsgBox "Xong! " & Str
End If
End If
Application.EnableEvents = True
End Sub
