Excel VBA AutoFilter用户选择运行时错误1004 [英] Excel VBA AutoFilter on user selection run-time error 1004

问题描述

此工作簿的目的是跟踪不同的工作，通过供应商或参考号，具有简单使用的前端（内容页面），仅由按钮和搜索框组成（其实际上不是单独的框，而仅仅是内容表单元格J8的内容（以下简称J8）。

按钮将按供应商类型进行过滤（并且工作完美），但用户选择我是遇到麻烦。

我的这个宏的代码是：

  Sub Find_Click（）
 Dim userSelect As String 
 userSelect =*& Range（J8）&*
表格（文件夹）选择
 ActiveSheet.Range（$ B $ 1：$ B $ 5000）。AutoFilter字段：= 2，条件：= userSelect，运算符：= x1And 
 End Sub 
  

当查找按钮为预设时sed，这应该读取J8，然后选择文件夹表并过滤结果，以显示列B中包含J8中文本的每个条目。

这工作正常。但是，现在当我尝试使用这个宏时，我得到一个1004的运行时错误与'应用程序定义或对象定义的错误'消息。

任何人都可以帮助？

编辑：

包含按照以下格式分配宏的按钮：

  Sub Button1_Click（）
表格（Folders）。选择
 ActiveSheet.Range（$ A $ 1：$ A $ 5000）AutoFilter字段：= 1，Criteria1：= _ 
条件
设置r =范围（范围（A3），范围（A3）结束（xlDown）） 
j = WorksheetFunction.CountA（r.Cells.SpecialCells（xlCellTypeVisible））
'MsgBox j 
如果j = 0然后
 MsgBox目前没有与Criteria有关的工作
 ActiveSheet.AutoFilterMode = False 
 ActiveSheet.Range（A3）。选择
表格（Contents）。选择
 End If 
 End Sub 
  

还有一个清除按钮，清除过滤器并返回到内容表：

  Sub Re set_Click（）
 ActiveSheet.ShowAllData 
表格（Contents）。选择
 End Sub 
  

解决方案

通常，您需要激活要使用 AutoFilter 。

此外，当您尝试使用带有通配符的AutoFilter（ * 或？）或数学测试，您需要在条件字符串的开头添加 = ，因此

  userSelect == *&范围（J8）& *
  

然后，它不是标准，但 Criteria1 和 Criteria2 如果您使用第二个！所以在这种情况下，您不需要一个运算符。

最后还有 ActiveSheet .Range（$ B $ 1：$ B $ 5000）。AutoFilter字段：= 2 ，您要求代码过滤在只有一列的范围的第二列！ p>

所以如果你想在col B上进行过滤，只需将 Field：= 2 更改为字段：= 1

---

以下是工作代码：

  Sub Find_Click（）
 Dim userSelect As String 
 Dim wS as Worksheet 
 userSelect == *&范围（J8）& *
设置wS =表（文件夹）
 wS.Activate 
 wS.Range（B1）。激活
如果不是wS.AutoFilterMode然后wS.AutoFilterMode = True 
 wS.Range（$ B $ 1：$ B $ 5000）。AutoFilter字段：= 1，Criteria1：= userSelect 
 End Sub 
  pre> 
 
 
而且您还在 xlAnd 中输入了错字，它是 x1And ;）
 
I have an Excel 2010 workbook containing 2 sheets ("Contents" and "Folders").

The purpose of this workbook is to track different pieces of work by supplier or reference number, with a front-end (the Contents page) that is simple to use, consisting only of buttons and a search box (Which isn't actually a separate box, but simply the contents of cell J8 of the Contents sheet (hereafter referred to as J8) as typed by the user.

The buttons will filter by supplier type (and work perfectly fine) but it's the user selection that I'm having trouble with.

My code for this macro is:
    Sub Find_Click()
    Dim userSelect As String
    userSelect = "*" & Range("J8") & "*"
    Sheets("Folders").Select
    ActiveSheet.Range("$B$1:$B$5000").AutoFilter Field:=2, Criteria:=userSelect, Operator:=x1And
    End Sub
When the 'Find' button is pressed, this should read J8, then select the Folders sheet and filter the result to show every entry in column B that contains the text in J8.

This was working fine.  However, now when I try to use this macro I get a 1004 run-time error with the 'Application-defined or object-defined error' message.

Can anyone please help?

EDIT:

The Contains buttons that have macros assigned that follow this format:
Sub Button1_Click()
Sheets("Folders").Select
ActiveSheet.Range("$A$1:$A$5000").AutoFilter Field:=1, Criteria1:= _
    "Criteria"
    Set r = Range(Range("A3"), Range("A3").End(xlDown))
j = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
'MsgBox j
If j = 0 Then
    MsgBox "There is currently no work relating to Criteria"
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Range("A3").Select
    Sheets("Contents").Select
End If
End Sub
There is also a resest button that clears a filter and returns to the Contents sheet:
Sub Reset_Click()
ActiveSheet.ShowAllData
Sheets("Contents").Select
End Sub

解决方案
Generally, you'll need to activate a cell inside the range in which you are going to use the AutoFilter.

Further more, when you are trying to use AutoFilter with wildcards (* or ?) or math test, you'll need to add an = at the start of your criteria string, so
userSelect = "=*" & Range("J8") & "*"
Then, it is not Criteria, but Criteria1 and Criteria2 if you use a second one! So you don't need an Operator in this case.

And finally with  ActiveSheet.Range("$B$1:$B$5000").AutoFilter Field:=2, you are asking the code to filter on the second column of a range where there is only one column!

So if you want to filter on col B, just change Field:=2 to Field:=1



Here is the working code :
Sub Find_Click()
Dim userSelect As String
Dim wS as Worksheet
userSelect = "=*" & Range("J8") & "*"
Set wS = Sheets("Folders")
wS.Activate
wS.Range("B1").Activate
If Not wS.AutoFilterMode Then wS.AutoFilterMode = True
wS.Range("$B$1:$B$5000").AutoFilter Field:=1, Criteria1:=userSelect
End Sub
And you also had a typo in xlAnd, it was x1And ;)

                        
这篇关于Excel VBA AutoFilter用户选择运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！