Run time error 1004 select method of worksheet class failed

[Aaron DeYoung]

I a, a newer VBA user and I am having trouble running a Recorded and 'somewhat cleaned up' script. The following is part of what I have and Bold and Italicized is the problem part that keeps popping up with the Run time error 1004. I have tried an array of different things, but nothing has worked. Any help would be greatly appreciated. Thanks in advance!

Selection.AutoFill Destination:=Range("I2:I4223"), Type:=xlFillDefault
Range("I2:I4223").Select
Columns("I").Select
Selection.NumberFormat = "mm/dd/yyyy"
Columns("R").Select
Selection.NumberFormat = "mm/dd/yyyy"

Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'Prior Month Training Report'!R[1]C[-12]:R[4218]C[5],13,FALSE)"
Selection.AutoFill Destination:=Range("M2:R2"), Type:=xlFillDefault
Range("M2:R2").Select
Selection.AutoFill Destination:=Range("M2:R4223"), Type:=xlFillDefault
Range("M2:R4223").Select



ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$1:$R$4223").AutoFilter

Rows("1:1").Select
Range("H1").Activate
ActiveSheet.Range("$A$1:$R$1").AutoFilter Field:=9, Criteria1:=Array( _
"#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1900")



Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("$A$1:$R$4223").AutoFilter Field:=9

 

[JLGWhiz]

hi Aaron DeYoung, welcome to the Forum

One thing that jumps out is that you are using a single row as the filter range, but are telling VBA to filter Field 9, which would be cell I1. I'm not sure what you are trying to filter in one cell.

 

[Aaron DeYoung]

JLGWhiz,

Thank you for the reply. Maybe that has something to do with it. I am trying to filter column I, and the results of the filter will then be deleted to clear some extraneous data that I do not need in the report.

When I Record the Macro it still doesn't run. It looks like this:



Rows("1:1").Select
Range("H1").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AD$4223").AutoFilter Field:=9, Criteria1:=Array( _
"#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1/0/1900")

I have no idea what is wrong. I have 16 columns of data and 4223 rows of data.

Should it say Filter Column 9?

 

[Andrew Poulsom]

What is the actual text of the error? That line of code isn't selecting a worksheet. And why do you have 2 criteria?

 

[Aaron DeYoung]

Forgive me for that, the Error is : Run Time error 1004 select method of range class failed.

I was trying to tweak the code a bit and that is why I was getting the 'method of worksheet' error.

 

[Andrew Poulsom]

Your code isn't selecting a range either. Selection (or Activation) isn't necessary.

 

[Aaron DeYoung]

Your code isn't selecting a range either. Selection (or Activation) isn't necessary.

This is what I am now using for the code:

Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'Prior Month Training Report'!R[1]C[-12]:R[4218]C[5],13,FALSE)"
Selection.AutoFill Destination:=Range("M2:R2"), Type:=xlFillDefault
Range("M2:R2").Select
Selection.AutoFill Destination:=Range("M2:R4223"), Type:=xlFillDefault
Range("M2:R4223").Select


ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$1:$R$4223").AutoFilter


ActiveSheet.Range("$A$1:$AD$4223").AutoFilter Column:=9, Criteria1:=Array( _
"#N/A"), Operator:=xlFilterValues, Criteria2:=Array(0, "1/0/1900")



Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("$A$1:$R$4223").AutoFilter Field:=9

ActiveSheet.Range("$A$1:$R$4223").AutoFilter Field:=16, Criteria1:="=0", _

Application defined or object defined error is what it is showing now. I have the debugged error in Bold. Thanks a ton for your help.

 

[Aaron DeYoung]

I am only wanting the filter for column 9 (I) to activate so that I can select certain filters to be deleted. I have tried everything it seems like.

 

[Andrew Poulsom]

You can't have Criteria2 with a Values Filter. All the criteria must be in the array passed to Criteria1.

 

[Aaron DeYoung]

You can't have Criteria2 with a Values Filter. All the criteria must be in the array passed to Criteria1.

Forgive me for being new and not understanding VBA as much as I would like to at the moment.

Should it look like:

Selection.AutoFilter
ActiveSheet.AutoFilter Field:=9, Criteria1:=Array( _
"#N/A", "1/0/1900"), Operator:=xlFilterValues

This still doesn't work.

Thank You.