Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr(), dArr(), iCol1(), i As Integer, k As Integer, Ngay, j As Integer, Rng As Range, KHO As String
If Target.Address = "$A$3" Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Arr = Sheet10.Range("A2:P" & Sheet10.Range("A65000").End(xlUp).Row).Value
ReDim dArr(1 To UBound(Arr, 1), 1 To 13)
Ngay = Target.Value: KHO = Sheet25.[$C$7]
iCol = Array(9, 4, 5, 6, 7, 10, 11, 14, 3, 8, 25, 1, 13)
For i = LBound(Arr, 1) To UBound(Arr, 1)
If (Ngay = Arr(i, 2)) And (KHO = Arr(i, 8)) Then
k = k + 1
For j = 0 To 12
If iCol(j) = 25 Then
dArr(k, j + 1) = Arr(i, 10) * Arr(i, 11) * (Arr(i, 12) + Arr(i, 13))
ElseIf iCol(j) = 3 Then
dArr(k, j + 1) = (dArr(k, j) * dArr(k, j - 2)) / 100
ElseIf iCol(j) = 8 Then
dArr(k, j + 1) = (dArr(k, j - 3) * dArr(k, j - 2)) / 100
ElseIf iCol(j) = 1 Then
dArr(k, j + 1) = dArr(k, j - 5) * dArr(k, j - 1) * dArr(k, j)
Else
dArr(k, j + 1) = Arr(i, iCol(j))
End If
Next j
End If
Next i
Sheet25.Range("A14:M" & (Sheet25.Range("A65000").End(xlUp).Row) + 5).Clear
If k <> 0 Then
With Sheet25
.Range("A14").Resize(k, 13).Value = dArr
.Sort.SortFields.Clear
.Range("A14:M" & (k + 14)).Sort Key1:=.Range("M14:M" & (k + 14)), order1:=xlDescending, Header:=xlNo
j = .Range("A65000").End(xlUp).Row
For i = j To 14 Step -1
If .Range("M" & i) <> .Range("M" & (i - 1)) Then
.Rows(i & ":" & i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
.Range("B" & i).Value = "Toång hoä baùn thöôøng xuyeân ñöôïc trôï giaù +" & .Range("M" & (i + 1)).Value & " ñoàng/TSC"
.Range("B" & i).Font.Name = "VNI-Times"
.Range("B" & i & ":L" & i).Font.Bold = True
End If
Next i
For Each Rng In .Range("L14:L" & .Range("L65000").End(xlUp).Row).SpecialCells(2).Areas
.Range("I" & (Rng.Row - 1)).Formula = "=sum(" & Replace(Rng.Offset(, -3).Address, "$", "") & ")"
.Range("J" & (Rng.Row - 1)).Formula = "=sum(" & Replace(Rng.Offset(, -2).Address, "$", "") & ")"
.Range("L" & (Rng.Row - 1)).Formula = "=sum(" & Replace(Rng.Address, "$", "") & ")"
Next
.Range("M14:M" & .Range("A65000").End(xlUp).Row).ClearContents
With .Range("A65000").End(xlUp)
.Offset(1).Value = "Toång giaù trò haøng hoùa ñöôïc mua vaøo "
.Offset(1).Resize(, 13).Font.Name = "VNI-Times"
.Offset(1).Resize(, 13).Font.Bold = True
.Offset(1).Resize(, 6).HorizontalAlignment = xlCenter
.Offset(1).Resize(, 6).Merge
.Offset(1, 8).Formula = "=SUMIF(R14C3:R[-1]C3,"""",R14C9:R[-1]C9)"
.Offset(1, 9).Formula = "=SUMIF(R14C3:R[-1]C3,"""",R14C10:R[-1]C10)"
.Offset(1, 11).Formula = "=SUMIF(R14C3:R[-1]C3,"""",R14C12:R[-1]C12)"
End With
.Range("A14:M" & .Range("A65000").End(xlUp).Row).Borders.LineStyle = 1
With .Range("A65000").End(xlUp).Offset(1)
.Value = "Baèng chöõ:"
.Offset(, 1).Formula = "=DocSoAbc(" & .Offset(-1, 11).Address & ")" 'Sua ham doc tieng viet cho nay
.Offset(2, 1).Value = "Ngöôøi laäp baûng keâ"
.Offset(2, 1).Font.Bold = True
.Offset(1, 11).Value = "Ngaøy ... thaùng .... naêm ......"
.Offset(2, 11).Value = "Giaùm ñoác danh nghieäp"
.Offset(2, 11).Font.Bold = True
.Offset(3, 1).Value = "(Kyù vaø ghi roõ hoï teân)"
.Offset(3, 11).Value = "(Kyù teân, ñoùng daáu)"
.Offset(2, 1).Resize(2).HorizontalAlignment = xlCenter
.Offset(1, 11).Resize(3).HorizontalAlignment = xlCenter
.Resize(5, 13).Font.Name = "VNI-Times"
End With
End With
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End If
End Sub
