Auto filtering erroring out

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
402
Office Version
  1. 2016
VBA Code:
Sub reconciliation()

'

' reconciliation Macro

'



'

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select

If ActiveSheet.AutoFilterMode Then

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

Else

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

End If



Range("AR12").Select



ActiveSheet.Range("$A$1:$Ax$6000").AutoFilter Field:=34, Criteria1:= _

"5 - ALC "

ActiveSheet.Range("$A$1:$Ax$6000").AutoFilter Field:=36, Criteria1:="="





Range("as2:as6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE("""",RC[-44],RC[-40])"



Range("at2:at6000").SpecialCells(xlCellTypeVisible).Formula = "=+TRIM(RC[-31])"



Range("au2:au6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RIGHT(RC[-1],4))"



Range("av2:av6000").SpecialCells(xlCellTypeVisible).Formula = "=+SUMIF(C[-1],RC[-1],C[-38])"



Range("aw2:aw6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RC[-1])"



Sheets("").Select



Range("BD2").Select

ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-50],RC[-47],RC[-45])"

Range("BD2").Select

Selection.AutoFill Destination:=Range("BD2:BD488"), Type:=xlFillDefault

Range("BD2:BD488").Select



Range("BE2").Select

ActiveCell.FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-42])"

Range("BE2").Select

Selection.AutoFill Destination:=Range("BE2:BE488")

Range("BE2:BE488").Select

Range("BF2").Select

ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-2],RC[-1])"

Range("BF2").Select

Selection.AutoFill Destination:=Range("BF2:BF488")

Range("BF2:BF488").Select

Range("BG2").Select

ActiveCell.FormulaR1C1 = "=+MATCH(RC[-1],'FEB FY21 IDARRS'!C[-10],0)"

Range("BG2").Select

Selection.AutoFill Destination:=Range("BG2:BG488")

Range("BG2:BG488").Select

Columns("BF:BF").EntireColumn.AutoFit

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select



Columns("AW:AW").ColumnWidth = 8.22

Columns("AW:AW").EntireColumn.AutoFit

Sheets("").Select

Range("BF3").Select

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select





Range("ax2:ax6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],!C[8],0)"





Sheets("").Select



Range("BD2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=+CONCATENATE("""",RC[-50],RC[-47],RC[-45])"

Range("BD2").Select

Selection.AutoFill Destination:=Range("BD2:BD488")

Range("BD2:BD488").Select

Range("BH2").Select

Selection.ClearContents

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select



With ActiveSheet

If ActiveSheet.AutoFilterMode Then

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

Else

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

End If

End With



ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=50, Criteria1:="<>#N/A", _

Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select





Range("aj2:aj6000").SpecialCells(xlCellTypeVisible).Formula = "COMPLETE"







Range("al2:al6000").SpecialCells(xlCellTypeVisible).Formula = ""





End Sub

here is the part it is erroring out on . seems like the range isnt activated or working right. Im not sure whats wrong though. ANy help is appreciated! 1004 autofilter method range of class failed is the error.

ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=50, Criteria1:="<>#N/A", _

Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
402
Office Version
  1. 2016
so the problem seems that there arent any #n/a in the column, how do i account for that in case there are or arent any #n/a values ?
 

Forum statistics

Threads
1,181,896
Messages
5,932,676
Members
436,850
Latest member
Jasperlee93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top