Auto filtering erroring out

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
323
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jordanburch

Active Member
Joined
Jun 10, 2016
Messages
323
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,621
Messages
5,659,922
Members
418,536
Latest member
Tezzies

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