设置自动过滤多个通配符 [英] Set Auto Filtering multiple wildcards

问题描述

现在我正在进行编码以设置数据图表的过滤器。基本上，我不知道如何在这里发布数据表，所以只是尝试输入它们）：

（从左边开始是列A）
名称* BDevice *数量*销售*所有者

基本上，我需要过滤出2列：
- 具有任何单词的BDevice包含M1454或M1467或M1879（这意味着M1454A或M1467TR仍然适用）
- 拥有PROD或RISK的所有者

这是代码I写道：

  Sub AutoFilter（）
 
 ActiveWorkbook.ActiveSheet..Range（B：B）。选择
 
 Selection.Autofilter字段：= 1 Criteria1：= Array（_ 
* M1454 *，* M1467 *，* M1879 *），运算符：= xlFilterValues 
 
 Selection.AutoFilter字段：= 4 Criteria1：== PROD_ 
，运算符：= xlOr，Criteria2：== RISK
 
 End Sub 
  

当我运行代码时，机器返回错误1004，部分似乎是错误的是过滤器第2部分（我不知道t他使用Field，所以我不能肯定地说）

编辑; Santosh：当我尝试你的代码，机器的错误9下标超出范围。错误来自with语句。 （因为数据表有A到AS列，所以我只是改为A：AS）

解决方案

虽然有最大
^{在应用AutoFilter方法之前}

^{After应用AutoFilter w /多个通配符}

---

¹_{请参阅可以将高级筛选条件置于VBA中而不是范围内？和可以使用自动过滤器从字典键中包含包含和不包含的通配符？更多关于添加排除字典的过滤器集。}

Right now I am doing coding to set a filter for a data chart. Basically, I don't know how to post the data sheet up here so just try to type them ):

(starting from the left is column A) Name * BDevice * Quantity * Sale* Owner

Basically I need to filter out for 2 column: -The BDevice with any word contain "M1454" or "M1467" or "M1879" (It means that M1454A or M1467TR would still fit in) -The Owner with PROD or RISK

Here is the code I wrote:

Sub AutoFilter()

  ActiveWorkbook.ActiveSheet..Range(B:B).Select

  Selection.Autofilter Field:=1 Criteria1:=Array( _
      "*M1454*", "*M1467*", "*M1879*"), Operator:=xlFilterValues

  Selection.AutoFilter Field:=4 Criteria1:="=PROD" _
      , Operator:=xlOr, Criteria2:="=RISK"

End Sub

When I run the code, the machine returns error 1004 and the part which seems to be wrong is the Filter part 2 ( I am not sure about the use of Field, so I can not say it for sure)

Edit; Santosh: When I try your code, the machine gets error 9 subscript out of range. The error came from the with statement. (since the data table has A to AS column so I just change to A:AS)

解决方案

While there is a maximum of two direct wildcards per field in the AutoFilter method, pattern matching can be used to create an array that replaces the wildcards with the Operator:=xlFilterValues option. A Select Case statement helps the wildcard matching.

The second field is a simple Criteria1 and Criteria2 direct match with a Operator:=xlOr joining the two criteria.

Sub multiWildcardFilter()
    Dim a As Long, aARRs As Variant, dVALs As Object

    Set dVALs = CreateObject("Scripting.Dictionary")
    dVALs.CompareMode = vbTextCompare

    With Worksheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            'build a dictionary so the keys can be used as the array filter
            aARRs = .Columns(2).Cells.Value2
            For a = LBound(aARRs, 1) + 1 To UBound(aARRs, 1)
                Select Case True
                    Case aARRs(a, 1) Like "MK1454*"
                        dVALs.Add Key:=aARRs(a, 1), Item:=aARRs(a, 1)
                    Case aARRs(a, 1) Like "MK1467*"
                        dVALs.Add Key:=aARRs(a, 1), Item:=aARRs(a, 1)
                    Case aARRs(a, 1) Like "MK1879*"
                        dVALs.Add Key:=aARRs(a, 1), Item:=aARRs(a, 1)
                    Case Else
                        'no match. do nothing
                End Select
            Next a

            'filter on column B if dictionary keys exist
            If CBool(dVALs.Count) Then _
                .AutoFilter Field:=2, Criteria1:=dVALs.keys, _
                                      Operator:=xlFilterValues, VisibleDropDown:=False
            'filter on column E
            .AutoFilter Field:=5, Criteria1:="PROD", Operator:=xlOr, _
                                  Criteria2:="RISK", VisibleDropDown:=False

            'data is filtered on MK1454*, MK1467* or MK1879* (column B)
            'column E is either PROD or RISK
            'Perform work on filtered data here
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    dVALs.RemoveAll: Set dVALs = Nothing
End Sub

If exclusions¹ are to be added to the filtering, their logic should be placed at the top of the Select.. End Select statement in order that they are not added through a false positive to other matching criteria.

        
                        ^{Before applying AutoFilter Method}

        
                        ^{After applying AutoFilter w/ multiple wildcards}

---

¹ _{See Can Advanced Filter criteria be in the VBA rather than a range? and Can AutoFilter take both inclusive and non-inclusive wildcards from Dictionary keys? for more on adding exclusions to the dictionary's filter set.}

这篇关于设置自动过滤多个通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！