Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, [E5:E7]) Is Nothing Then
Application.ScreenUpdating = False
Dim iR As Long, endR As Long, sR As Long, sSHTK As String, Thang
Dim ArrData(), ArrSocai(), MKH As String
With Sheets("NKC")
endR = .Cells(65000, 2).End(xlUp).Row
ArrData = .Range("A4:J" & endR).Value
End With
ReDim ArrSocai(1 To endR, 1 To 9)
With Sheets("Socai")
sSHTK = CStr(.Range("E5").Value)
MKH = .Range("E6").Value
.Range("A11:I6500").Clear
Thang = .Range("E7").Value
End With
For iR = 1 To UBound(ArrData)
If ArrData(iR, 4) = Thang Then
If MKH <> "" Then
If ArrData(iR, 5) = MKH Then
If ArrData(iR, 8) Like sSHTK & "*" Or ArrData(iR, 9) Like sSHTK & "*" Then
sR = sR + 1
ArrSocai(sR, 1) = ArrData(iR, 1)
ArrSocai(sR, 2) = ArrData(iR, 2)
ArrSocai(sR, 3) = ArrData(iR, 3)
ArrSocai(sR, 4) = ArrData(iR, 4)
ArrSocai(sR, 5) = ArrData(iR, 7)
ArrSocai(sR, 7) = ArrData(iR, 5)
If ArrData(iR, 8) Like sSHTK & "*" Then
ArrSocai(sR, 6) = ArrData(iR, 9)
ArrSocai(sR, 8) = ArrData(iR, 10)
Else
ArrSocai(sR, 6) = ArrData(iR, 8)
ArrSocai(sR, 9) = ArrData(iR, 10)
End If
End If
End If
Else
If ArrData(iR, 8) Like sSHTK & "*" Or ArrData(iR, 9) Like sSHTK & "*" Then
sR = sR + 1
ArrSocai(sR, 1) = ArrData(iR, 1)
ArrSocai(sR, 2) = ArrData(iR, 2)
ArrSocai(sR, 3) = ArrData(iR, 3)
ArrSocai(sR, 4) = ArrData(iR, 4)
ArrSocai(sR, 5) = ArrData(iR, 7)
ArrSocai(sR, 7) = ArrData(iR, 5)
If ArrData(iR, 8) Like sSHTK & "*" Then
ArrSocai(sR, 6) = ArrData(iR, 9)
ArrSocai(sR, 8) = ArrData(iR, 10)
Else
ArrSocai(sR, 6) = ArrData(iR, 8)
ArrSocai(sR, 9) = ArrData(iR, 10)
End If
End If
End If
End If
Next
If sR Then
With Sheets("SOCAI").Range("A11")
.Resize(sR + 1, 9).Value = ArrSocai
.Offset(, 7).Resize(sR, 2).NumberFormat = "#,##0"
.Offset(, 2).Resize(sR, 1).NumberFormat = "mm/dd/yyyy"
.Offset(sR + 1, 4).Value = "Phát sinh trong k" & ChrW(7923)
.Offset(sR + 2, 4).Value = "S" & ChrW(7889) & " d" & ChrW(432) & " cu" & ChrW(7889) & "i k" & ChrW(7923)
.Offset(sR + 1, 7).Value = "=SUM(R11C:R" & sR + 10 & "C)"
.Offset(sR + 1, 8).Value = "=SUM(R11C:R" & sR + 10 & "C)"
.Offset(sR + 2, 7).Formula = "=Max(0,H10-I10+H" & 12 + sR & "-I" & sR + 12 & ")"
.Offset(sR + 2, 8).Formula = "=Max(0,-(H10-I10+H" & 12 + sR & "-I" & sR + 12 & "))"
BorderS Sheets("Socai").Range("A11").Resize(sR + 1, 9)
BorderS Sheets("Socai").Range("A11").Offset(sR + 1, 0).Resize(2, 9)
End With
End If
Erase ArrData, ArrSocai
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
