在VBA自动过滤器中使用字符串数组作为标准 [英] Using string array as criteria in VBA autofilter

问题描述

我搜索过其他帖子，发现类似的问题，但没有什么可以帮助我具体。我正在尝试使用一个字符串数组，并将其用作过滤条件。这是棘手的，因为数组是由一个函数创建的，并具有可变数量的元素和内容。我需要自动过滤器来检查它，并为其每个元素检查列E 。

I've searched other posts and found similar issues but nothing that could help me specifically. I'm trying to take an array of strings and use it as a filter criteria. It's tricky because the array is created by a function and has a variable number of elements and contents. I need the autofilter to take it and check column E for each one of its elements.

我尝试过两种方式

1）

With Sheet17

 .Range("E1").AutoFilter Field:=5, Criteria1:=Application.Transpose(arr)

End With

结果：将过滤器应用于列E，但无法选择任何选项

Result: Applies a filter to column E but fails to select any of the options

2）

For i = 0 To counter - 1

  With Sheet17

    .Range("E1").AutoFilter Field:=5, Criteria1:=Application.Transpose(arr(i))
End With

Next

注意：Counter是一个整数，表示元素的数量数组
结果：这一个正确循环遍历数组，但只选择过滤器上的最后一个选项 - 大概是因为每次循环返回时，它都会重新开始，并取消选中其他选项，所以最后只有最近的选项保持检查。

Note: Counter is an integer representing the number of elements in the array Result: This one correctly loops through the array but only selects the last option on the filter - presumably because every time it loops back through it starts over and unchecks every other option so by the end only the most recent option remains checked.

推荐答案

你不需要转置唱歌如果您仅仅引用列E，则不能将条件放入5 ^th 字段。

You do not need to transpose a single element from an array and you cannot put criteria into the 5^th field if you are only referencing column E.

Dim i As Long, arr As Variant
arr = Array(1, 3)

With Sheet17
    'to filter each value in the array one at a time
    For i = 0 To UBound(arr)
        .Columns("E").AutoFilter Field:=1, Criteria1:=arr(i)
    Next i

    'my values were numbers - AutoFilter likes strings in its array
    For i = LBound(arr) To UBound(arr)
        arr(i) = CStr(arr(i))
    Next i

    'to filter all values in the array at once specify xlFilterValues
    .Columns("E").AutoFilter Field:=1, Criteria1:=arr, _
                             Operator:=xlFilterValues
End With

指定运算符：= xlFilterValues 在传递数组和 Rang e.AutoFilter方法将字符串作为数组中的值。

Specify the Operator:=xlFilterValues when passing an array and the Range.AutoFilter Method likes strings as the values in an array.

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