Sheets("MAY FY20 IDARRS").Select
With Worksheets("MAY FY20 IDARRS")
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
Else
If .FilterMode Then
.ShowAllData
End If
End If
End With
Dim d As Object
Dim a As Variant
Dim i As Long
Set d = CreateObject("Scripting.Dictionary")
Sheets("MAY FY20 IDARRS").Select
With Range("A1:Ax" & Range("I" & Rows.Count).End(xlUp).Row + 1)
a = .Columns(9).Value
For i = 2 To UBound(a)
Select Case a(i, 1)
Case "<>8522"
Case Else: d(a(i, 1) & "") = 1
End Select
Next i
.AutoFilter Field:=9, Criteria1:=d.keys(), Operator:=xlFilterValues
End With
ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=36, Criteria1:="="
ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=6, Criteria1:="=1110" _
, Operator:=xlOr, Criteria2:="=2220"
' ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=9, Criteria1:="<>8522"
'
Sheets("HCFCDA").Select
Range("AE2").Select
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.FormulaR1C1 = "=+CONCATENATE(""DELMARERROR"",RC[-5])"
Range("AE2").Select
Selection.AutoFill Destination:=Range("AE2:AE5291")
Range("AE2:AE5291").Select
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=+COUNTIF(C[-1],RC[-1])"
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=+COUNTIF(RC[-1]:R2C31,RC[-1])"
Range("AF2").Select
Selection.AutoFill Destination:=Range("AF2:AF5291")
Range("AF2:AF5291").Select
Range("AG2").Select
ActiveCell.FormulaR1C1 = "=+MATCH(RC[-2],RC[-1])"
Range("AG2").Select
ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-2],RC[-1])"
Range("AG2").Select
Selection.AutoFill Destination:=Range("AG2:AG5291")
Range("AG2:AG5291").Select
Sheets("MAY FY20 IDARRS").Select
Range("as2:as6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(""DELMARERROR"",RC[-35])"
Range("at2:at6000").SpecialCells(xlCellTypeVisible).Formula = "=+IF(ISNUMBER(RC[-36]),COUNTIF(RC[-1]:R71C45,RC[-1]), "" "")"
Range("au2:au6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RC[-1])"
Sheets("HCFCDA").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AH2").Select
ActiveCell.FormulaR1C1 = "=+MATCH(RC[-1],'MAY FY20 IDARRS'!C[13],0)"
Range("AH2").Select
Selection.AutoFill Destination:=Range("AH2:AH5291")
Range("AH2:AH5291").Select
Cells.Select
Range("O1").Activate
Selection.AutoFilter
Sheets.Add.Name = "DELMAR ERROR"
Sheets("HCFCDA").Select
ActiveSheet.Range("$A$1:$AO$6000").AutoFilter Field:=34, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues
Cells.Select
Range("L1").Activate
Selection.Copy
Sheets("DELMAR ERROR").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("MAY FY20 IDARRS").Select
Range("av2:av6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'DELMAR ERROR'!C[-15],0)"
Range("a1:ax6000").Activate
ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=48, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues
Range("aj2:aj6000").SpecialCells(xlCellTypeVisible).Formula = "COMPLETE"
Range("al2:al6000").SpecialCells(xlCellTypeVisible).Formula = "DELMAR ERROR"
Sheets("MAY FY20 IDARRS").Select