Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

excel - VBA Autofilter With Multiple Criteria Using Variables

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...