Arunachaljois
Board Regular
- Joined
- Aug 1, 2020
- Messages
- 57
- Office Version
- 365
- Platform
- Windows
Hello,
In my VBA code there is a loop in column Y and there is formula in Column Z
Then i want to filter data "Success" in column Z
But it select only "Success" but not whatever the loop data in column Y
For example if i filtered manually "Success" there total number of rows filtered is 675
If i use macro then only 478 rows are visible
Could anyone please help me on this ? Below code is for your reference.
In my VBA code there is a loop in column Y and there is formula in Column Z
Then i want to filter data "Success" in column Z
But it select only "Success" but not whatever the loop data in column Y
For example if i filtered manually "Success" there total number of rows filtered is 675
If i use macro then only 478 rows are visible
Could anyone please help me on this ? Below code is for your reference.
VBA Code:
For Each cell In Range("I2:I" & LR)
If IsError(Range("Z" & cell.Row).Value) Then
If cell.Value = "W100" Then
Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
ElseIf cell.Value = "W102" Then
Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
Else
Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
End If
End If
Next cell
If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
With Sheets("PIR Template")
With .Range("A1:AH1")
.AutoFilter
.AutoFilter Field:=26, Criteria1:="Error"
End With
End With
Range("AA2:AA" & LR).Formula = "=VLOOKUP(RC[-2],ZLOE019!C1:C7,7,0)"
Application.CutCopyMode = False
Sheets("PIR Template").AutoFilterMode = False
End If
If WorksheetFunction.CountIf(Range("Z:Z"), "Success") > 0 Then
With Sheets("PIR Template")
With .Range("A1:AH1")
.AutoFilter
.AutoFilter Field:=26, Criteria1:="Success"
End With
End With
End if