excel - VBA Autofilter With Multiple Criteria Using Variables

Question

EDIT: To fix the issue, I changed Dim Placed As Range to As Long. I then changed

Set Placed = Rows("3:3").Find("Placed", Range("A3"), searchdirection:=xlToRight)

to

Placed = Rows("3:3").Find("Placed", Range("A3"), searchdirection:=xlToRight).Column

The code works just fine now. END EDIT

Ok, I've been working on this issue for almost two hours now.

I'm trying to code a couple of option buttons on a worksheet to filter the data as needed.

To begin, I recorded myself filtering the data to give me a starting point. This is what the recorder spit out:

ActiveSheet.Range("$A$3:$CS$212").AutoFilter Field:=53, Criteria1:=Array( _
    "Iteration 1", "Iteration 2", "Iteration 3", "Tradeshow", "="), Operator:= _
    xlFilterValues

To make the option buttons more robust, I decided to use variables just in case columns or rows were added, or if criteria were added.

I added a variable for the Range(), Field:=, and Criteria1:=, but my code throws this error now: Run-time error '1004': Autofilter Method of Range class failed.

I'm wondering if I'm using the Array improperly...? Anyway, here are my declarations:

Const Opt1 As String = "Iteration 1"
Const Opt2 As String = "Iteration 2"
Const Opt3 As String = "Iteration 3"
Const Opt4 As String = "Iteration 4"
Const Opt5 As String = "Tradeshow"
Const Opt6 As String = "Placed"

Dim Placed As Range             'This is the Field var.
Dim lastRow, lastColumn As Long 'Holds the last row and column numbers.
Dim Rng1, Rng2 As Range         'These hold the beginning and ending ranges for the filter

And here's how I'm setting my variables:

lastRow = Range("A:A").Find("*", Range("A1"), searchdirection:=xlPrevious).Row
lastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
Set Placed = Rows("3:3").Find("Placed", Range("A3"), searchdirection:=xlToRight)
Set Rng1 = Cells(3, 1)
Set Rng2 = Cells(lastRow, lastColumn)

Finally, here is the AutoFilter code:

ActiveSheet.Range(Rng1, Rng2).AutoFilter Field:=Placed, Criteria1:=Array(Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, "="), Operator:=xlFilterValues

Does anyone see why it might be throwing that error? Does it have something to do with the Array? Any help will be much appreciated!

Answer 1

No, it's an issue with the Field:= parameter. You have it set to Placed, and you have assigned the Placed variable to a cell. It needs to be a column number. For example, if the Placed column is column D and your data starts in column A, the Field should be 4, because it is the fourth column. If the placed column is the last column, you could set Field equal to your lastColumn variable:

ActiveSheet.Range(Rng1, Rng2).AutoFilter Field:=lastColumn, Criteria1:=Array(Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, "="), Operator:=xlFilterValues