使用变量的具有多个标准的 VBA 自动过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18135144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Autofilter With Multiple Criteria Using Variables
提问by ARich
EDIT: To fix the issue, I changed Dim Placed As Rangeto As Long. I then changed
编辑：要解决这个问题，我换Dim Placed As Range到As Long。然后我改变了
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:$CS2").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.
我加了一个可变的Range()，Field:=以及Criteria1:=，但我的代码现在抛出这个错误：Run-time error '1004': Autofilter Method of Range class failed。
I'm wondering if I'm using the Arrayimproperly...? Anyway, here are my declarations:
我想知道我是否使用Array不当...？无论如何，这是我的声明：
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 AutoFiltercode:
最后，这里是AutoFilter代码：
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!
有没有人明白为什么它可能会抛出那个错误？和那个有关系Array吗？任何帮助都感激不尽！
回答by tigeravatar
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:
不，这是 Field:= 参数的问题。您已将其设置为已放置，并且已将已放置变量分配给一个单元格。它必须是列号。例如，如果 Placed 列是 D 列并且您的数据从 A 列开始，则字段应为 4，因为它是第四列。如果放置的列是最后一列，您可以将 Field 设置为等于您的 lastColumn 变量：
ActiveSheet.Range(Rng1, Rng2).AutoFilter Field:=lastColumn, Criteria1:=Array(Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, "="), Operator:=xlFilterValues
