VBA自动过滤器使用变量多标准 [英] VBA Autofilter With Multiple Criteria Using Variables

问题描述

编辑：为了解决这个问题，我将 Dim Placed As Range 更改为 As Long 。设置放置=行（3：3）查找（放置，范围（A3），然后更改

 ），searchdirection：= xlToRight）
  

到

 放置=行（3：3）。Find（Placed，Range（A3），searchdirection：= xlToRight）.Column 
  

代码工作正常。
END EDIT

好的，我已经在这个问题上工作了近两个小时了。

我正在尝试在工作表上编写几个选项按钮以根据需要过滤数据。

首先，我记录了自己过滤数据给我一个起点这是录音机吐出来的：

  ActiveSheet.Range（$ A $ 3：$ CS $ 212）AutoFilter字段： = 53，Criteria1：= Array（_ 
Iteration 1，Iteration 2，Iteration 3，Tradeshow，=），运算符：= _ 
 xlFilterValues 
为了使选项按钮更加健壮，我决定使用变量来防止添加列或行，或者如果标准我添加了一个变量为 Range（）， Field：= 和 Criteria1：= ，但是我的代码现在会抛出此错误：运行时错误'1004'：自动过滤器方法的范围类失败。
 
 
 
我想知道我是否使用 Array 不正确？无论如何，这里是我的声明：
  Const Opt1 As String =Iteration 1
 Const Opt2 As String =迭代2
 Const Opt3 As String =Iteration 3
 Const Opt4 As String =Iteration 4
 Const Opt5 As String =Tradeshow
 Const Opt6 As String = 放置
 
 Dim Placed As Range'这是Field var。 
 Dim lastRow，lastColumn As Long'保留最后一行和列号。 
 Dim Rng1，Rng2 As Range'这些保存过滤器的开始和结束范围
  
这是我如何设置我的变量：
  lastRow = Range（A：A）Find（* ，Range（A1），searchdirection：= xlPrevious）.Row 
 lastColumn = Cells（3，Columns.Count）.End（xlToLeft）.Column 
设置放置=行（3：3 ）Find（Placed，Range（A3），searchdirection：= xlToRight）
设置Rng1 =单元格（3，1）
设置Rng2 =单元格（lastRow，lastColumn）
  
最后，这里是 AutoFilter 代码：
  ActiveSheet.Range（Rng1，Rng2）.AutoFilter字段：=放置，Criteria1：= Array（Opt1，Opt2，Opt3，Opt4，Opt5， Opt6，=），运算符：= xlFilterValues 
  
有谁知道为什么会抛出这个错误？是否与 Array 有关系？任何帮助将非常感谢！
解决方案
不，这是Field：=参数的问题。您将其设置为已放置，并将已放置变量分配给单元格。它需要是列号。例如，如果放置列是列D，并且数据从列A开始，则字段应为4，因为它是第四列。如果放置的列是最后一列，您可以将Field设置为与您的lastColumn变量相等：
  ActiveSheet.Range（Rng1，Rng2 ）.AutoFilter字段：= lastColumn，Criteria1：= Array（Opt1，Opt2，Opt3，Opt4，Opt5，Opt6，=），运算符：= xlFilterValues 
  
 
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!
解决方案
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


                        
这篇关于VBA自动过滤器使用变量多标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！