Sub Test2()
Application.ScreenUpdating = False
Dim a(), b(), i, j, k, DK, LR, LRow
With Sheets("BK_NX")
a = .Range("A6", .Range("B65000").End(3)).Resize(, 14).Value
LR = UBound(a)
End With
ReDim b(1 To LR, 1 To 11)
With Sheets("BK_NX")
DK = Sheets("THEKHO").Range("G4").Value
For i = 1 To LR
If a(i, 8) = DK Then
k = k + 1
b(k, 1) = k: b(k, 2) = a(i, 2): b(k, 3) = a(i, 3)
b(k, 4) = a(i, 4): b(k, 5) = a(i, 5): b(k, 6) = a(i, 10)
b(k, 7) = a(i, 11)
b(k, 9) = a(i, 12)
End If
Next i
With Sheets("THEKHO")
.Range("A11:k1000").ClearContents
.Range("A11:k1000").Borders.LineStyle = 0
End With
If k Then
With Sheets("THEKHO")
.Range("A11").Resize(k, 11) = b
.Range("A11").Resize(k, 11).Borders.LineStyle = 1
End With
End If
End With
With Sheets("THEKHO")
LRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("H11:H" & LRow).Formula = "= IF(B11="""","""",SUM($F$11:F11)-SUM($G$11:G11))"
.Range("J11:J" & LRow).Formula = "=IF(COUNTIF($I$11:I11,I11)=1,I11,"""")"
.Range("K11:K" & LRow).Formula = "=IF(J11="""","""",SUMIF($I$11:$I$1100,J11,$F$11:$F$1100)-SUMIF($I$11:$I$1100,J11,$G$11:$G$1100))"
.Range("H11:H" & LRow).Value = .Range("H11:H" & LRow).Value
.Range("J11:J" & LRow).Value = .Range("J11:J" & LRow).Value
.Range("K11:K" & LRow).Value = .Range("K11:K" & LRow).Value
End With
End Sub
