编辑:为解决此问题,我将 Dim Placed As Range
更改为 As Long
。然后我更改了
Set Placed = Rows("3:3").Find("Placed", Range("A3"), searchdirection:=xlToRight)
往下
Placed = Rows("3:3").Find("Placed", Range("A3"), searchdirection:=xlToRight).Column
代码现在运行良好。
好的,我已经花了将近两个小时来解决这个问题。
我正在尝试编写一些选项按钮以根据需要筛选数据。
首先,我录制了自己过滤数据,以便给我一个起点。这是录制器输出的:
ActiveSheet.Range("$A$3:$CS$212").AutoFilter Field:=53, Criteria1:=Array( _
"Iteration 1", "Iteration 2", "Iteration 3", "Tradeshow", "="), Operator:= _
xlFilterValues
为了使选项按钮更加健壮,我决定使用变量,以防添加列或行,或者添加条件。
我为
Range()
、Field:=
和 Criteria1:=
添加了一个变量,但是现在我的代码会抛出这个错误: Run-time error '1004': Autofilter Method of Range class failed
。我想知道我是否不正确地使用了
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
这是我设置变量的方法:
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)
最后,这里是 AutoFilter
代码:
ActiveSheet.Range(Rng1, Rng2).AutoFilter Field:=Placed, Criteria1:=Array(Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, "="), Operator:=xlFilterValues
有人知道为什么会出现错误吗?这与Array
有关系吗?非常感谢您的帮助!