VBA：自动筛选偏字符串数组 [英] VBA: Autofilter for Partial Array strings

问题描述

我要筛选我做了数组列。我试图用 Cells.AutoFilter现场：= 14，标准1：=字符串数组，接线员：= ，但我不知道运营商应该是什么

I need to filter a column for the array I've made. I'm trying to use Cells.AutoFilter Field:=14, Criteria1:=stringArray, Operator:= but I don't know what the operator should be.

我的问题的一个例子是，东西在我的阵列可能是他时，什么在我自动筛选列实际上是Tawm。我想类似运营商：= xlContains 但是这是一个不走

An example of my issue is that something in my Array might be "Ta" when what's in the column I'm autofiltering is actually "Tawm". I'm thinking something like Operator:=xlContains but that's a no-go.

我只是希望它就像我在TA我打字，然后选择所有自动筛选发现的选项。

I just want it to be like I'm typing in "Ta" and then selecting all the options which the autofilter finds.

我试过*添加到具有以下code数组中的每个条目，但它似乎并没有帮助：

I've tried to add "*" to each entry in the array with the following code but it doesn't seem to help:

Dim stringArray As Variant
Dim tempMfr As String
Dim temp2Mfr As String
Dim t As Variant

tempMfr = xCell & "*"
temp2Mfr = xCell.Offset(0, 2) 'this cell may have multiple entries such as "a, b, c"
stringArray = Split(temp2Mfr, ", ")
For Each t In stringArray
t = t & "*"

Cells.AutoFilter Field:=14, Criteria:=stringArray, Operator:=xlFilterValues

---

有没有更好的方法来做到这一点？

---

Is there a better way to do this?

感谢。

推荐答案

您不能在的自动筛选方法，然后将它必须像要么，

You cannot use wildcards in more than two criteria of the AutoFilter Method and then it must be like either,

.AutoFilter Field:=1, Criteria1:="=abc*", Operator:=xlAnd, Criteria2:="=def*"
' or,
.AutoFilter Field:=1, Criteria1:="=abc*", Operator:=xlOr, Criteria2:="=def*"

通过数组中的元素分裂串入一个变量数组，只需循环并添加星号之后（例如： CHR（42））的每个元素。

Sub Macro3()
    Dim v As Long, vFilters As Variant, sFilters As String
    Dim xCell As Range

    With Sheets("Sheet1")
        Set xCell = .Range("ZZ99")
        sFilters = xCell.Offset(0, 2) 'this cell may have multiple entries such as "a, b, c"
        vFilters = Split(sFilters, ", ")
        With .Cells(1, 1).CurrentRegion
            For v = LBound(vFilters) To LBound(vFilters)
                .Cells.AutoFilter Field:=14, Criteria:=vFilters(v) & Chr(42) 'begins with filter
                'move down a row to save the header and resize -1 row
                With .Resize(.Rows.Count - 1, .Columns.Count).offswet(1, 0)
                    'check if there is anything visible
                    If CBool(Application.Subtotal(103, .Columns(14))) Then
                        'do something with
                        '.Cells.SpecialCells (xlCellTypeVisible)
                    End If
                End With
            Next v
        End With
    End With

End Sub

如果你需要两个以上的通配符一次标准，编码一个显著更大的量可以被用来建立一个数组来传递到过滤器，然后使用运营商：= xlFilterValues​​ 选项。

If you need more than two 'wildcarded' criteria at once, a significantly larger amount of coding can be used to build an array to pass into the filter and then use the Operator:=xlFilterValues option.

这篇关于VBA：自动筛选偏字符串数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！