Hi,
In the below attached code (): When I am filtering data on KAX2, Even though it has no KAX2 data and it has already existing data for KAX1 , it is providing the same sum as KAX1.
Please help me on this.
Code
Sub Test()
Dim Rng As Range, A As Variant, e As Variant, sht As Worksheet, n As Long, i As Long
On Error Resume Next
Set sht = ThisWorkbook.Sheets("Dispatch_load")
If sht Is Nothing Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Dispatch_load"
End If
n = 10: i = 0
With Sheets("AMC Agent Breakout")
.Activate
For Each e In Array("KAX0", "KAX1", "KAX2", "KAX3", "KAX4", "KAX5", "KAX6", "KAX7", "KAX8", "KAX9")
.Range("A1:R" & .Range("A" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=8, Criteria1:=e
.Range("A1:R" & .Range("A" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=18, Criteria1:="0"
Set Rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, .AutoFilter.Range.Columns.Count) _
.SpecialCells(xlCellTypeVisible)
If Not Rng Is Nothing Then
On Error Resume Next
Set sht = ThisWorkbook.Sheets("Agent&DRM")
If sht Is Nothing Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Agent&DRM"
End If
With Sheets("Agent&DRM")
.Activate
Rng.Copy .Range("C6")
.Columns("C:I").Delete Shift:=xlToLeft
.Columns("D:J").Delete Shift:=xlToLeft
Range("C5").Select
ActiveCell.FormulaR1C1 = "KCODE"
Range("D5").Select
ActiveCell.FormulaR1C1 = "Count"
.Columns("E:F").Delete Shift:=xlToLeft
.Range("C5:D5" & .Range("C" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=e
.Range("T1").FormulaR1C1 = "AAX" & i
Range("U1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[5]C[-17]:R[2779]C[-17])"
.Range("T1:U1").Copy
Sheets("Dispatch_load").Range("A" & n).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End If
n = n + 1: i = i + 1
.AutoFilterMode = False
Next
End With
End Sub