Private Sub CommandButton1_Click()
Dim WF As WorksheetFunction, myStr As String
Set WF = WorksheetFunction
With Sheets("NHAPSANLUONG")
.AutoFilterMode = False
End With
TongCong = 0
On Error Resume Next
Sheets("NHAPSANLUONG").ShowAllData
Sheets("NHAPSANLUONG").[I:I].ClearContents
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
Dim endC As Long
With Sheets("BaoCao")
endC = .Cells(2, 256).End(xlToLeft).Column
.Cells(2, 2).Resize(, endC - 1).Name = "rngKho"
End With
myStr = Join(WF.Transpose(WF.Transpose(Range("rngkho").Value)), " ")
myStr = WF.Trim(myStr)
myStr = Replace(myStr, " ", ", ")
If WF.CountA(Range("rngKho")) > 1 Then
myStr = "Caïc kho: " & myStr
Else
myStr = "Kho: " & myStr
End If
Sheets("NHAPSANLUONG").[I1].Value = "GPE"
Sheets("NHAPSANLUONG").Range(Sheets("NHAPSANLUONG").[I2], Sheets("NHAPSANLUONG").[D65536].End(xlUp).Offset(, 5)).FormulaR1C1 = _
"=AND(RC[-5]<>"""",COUNTIF(rngKho,RC[-5])>0,RC[-7]>=BAOCAO!R3C2,RC[-7]<=BAOCAO!R3C4,OR(BAOCAO!R4C3=""Táút caí"",LEFT(RC[-3])=LEFT(BAOCAO!R4C3)))"
'Sheets("NHAPSANLUONG").Range(Sheets("NHAPSANLUONG").[I2], Sheets("NHAPSANLUONG").[D65536].End(xlUp).Offset(, 5)).FormulaR1C1 = "=AND(RC[-5]<>"""",OR(RC[-5]=BAOCAO!R2C2,RC[-5]=BAOCAO!R2C3,RC[-5]=BAOCAO!R2C4,RC[-5]=BAOCAO!R2C5,RC[-5]=BAOCAO!R2C6,RC[-5]=BAOCAO!R2C7,),RC[-7]>=BAOCAO!R3C2,RC[-7]<=BAOCAO!R3C4,OR(BAOCAO!R4C3=""Táút caí"",LEFT(RC[-3])=LEFT(BAOCAO!R4C3)))"
Sheets("NHAPSANLUONG").Range(Sheets("NHAPSANLUONG").[I2], Sheets("NHAPSANLUONG").[I65536].End(xlUp)) = Sheets("NHAPSANLUONG").Range(Sheets("NHAPSANLUONG").[I2], Sheets("NHAPSANLUONG").[I65536].End(xlUp)).Value
Application.Calculation = xlCalculationManual
'With Range("A11:G65536,H:I")
With Range("A11:G65536,H11:I65536")
.ClearContents
.Font.Bold = False
End With
Sheets("NHAPSANLUONG").Range("$A$1:$I$65536").AutoFilter
Sheets("NHAPSANLUONG").Range("$A$1:$I$65536").AutoFilter Field:=9, Criteria1:="=True"
Sheets("NHAPSANLUONG").Range("$G$2:$G$65536").SpecialCells(xlCellTypeVisible).Copy [H11]
Range([H11], [H65536].End(xlUp)) = Range([H11], [H65536].End(xlUp)).Value
Application.Calculation = xlCalculationAutomatic
Range([I11], [H65536].End(xlUp).Offset(, 1)).FormulaR1C1 = "=MATCH(RC[-1],MALH!R2C3:R" & Sheets("NHAPSANLUONG").[C65536].End(xlUp).Row & "C3,)"
Application.Calculation = xlCalculationManual
Range([H11], [I65536].End(xlUp)).Sort [I11], 1, Header:=xlNo
[H10].Formula = "=H11&"" GPE"""
k = 11
l = 10
For Each Rng In Range([H11], [H65536].End(xlUp).Offset(1))
If Left(Rng, 1) <> Left(Rng.Offset(-1), 1) Then
Range(Cells(k, 1), Cells(k, 6)).Font.Bold = True
Cells(k, 2) = "Täøng " & IIf(Rng.Offset(-1) = " GPE", [C4].Value, Left(Rng.Offset(-1), InStr(1, Rng.Offset(-1), " ") - 1))
Cells(k, 6) = Application.WorksheetFunction.Sum(Range([F11], Cells(k - 1, 6))) - TongCong * 2
TongCong = Application.WorksheetFunction.Sum(Range([F11], Cells(k - 1, 6))) - TongCong
l = k
k = k + 1
Cells(k, 1) = k - l
Cells(k, 2) = Rng.Value
Sheets("NHAPSANLUONG").Range("$A$1:$I$65536").AutoFilter Field:=7, Criteria1:=Rng.Value
Cells(k, 6) = Application.WorksheetFunction.Subtotal(9, Sheets("NHAPSANLUONG").Range("$H$2:$H$65536"))
k = k + 1
ElseIf Rng <> Rng.Offset(-1) Then
Cells(k, 2) = Rng.Value
Sheets("NHAPSANLUONG").Range("$A$1:$I$65536").AutoFilter Field:=7, Criteria1:=Rng.Value
Cells(k, 6) = Application.WorksheetFunction.Subtotal(9, Sheets("NHAPSANLUONG").Range("$H$2:$H$65536"))
Cells(k, 1) = k - l
k = k + 1
End If
Next
[F65536].End(xlUp).Offset(, -5).Resize(1, 7).ClearContents
Range([E11], [B65536].End(xlUp).Offset(, 3)).FormulaR1C1 = "Chiãúc"
With [B65536].End(xlUp)
.Offset(4) = "PHUÛ TRAÏCH ÂÅN VË"
.Offset(4, 2) = "THUÍ KHO"
.Offset(4, 4) = "NGÆÅÌI NHÁÛP"
End With
'[H:I].Clear
[H10:I65000].Clear
Sheets("NHAPSANLUONG").ShowAllData
Sheets("NHAPSANLUONG").[I:I].ClearContents
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub