将字符串变量传递给自动过滤条件数组 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20148294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass a string variable to an auto filter criteria array VBA
提问by Mr_Oppenheimer
Once again i am in trouble when trying to use array criteria in autofilters. Maybe someone will be able to solve it.
尝试在自动过滤器中使用数组条件时，我再次遇到了麻烦。也许有人能够解决它。
I have strings of criteria in an excel sheet in the following format: 12562,15215 (these are edited by users).
我在 Excel 表中有以下格式的条件字符串：12562,15215（这些由用户编辑）。
What i want to do is for a loop to take each cell containing these criteria and autofilter a certain field in another workbook using the cell contents as an array, then go to a next row of criteria.
我想要做的是循环获取包含这些条件的每个单元格，并使用单元格内容作为数组自动过滤另一个工作簿中的某个字段，然后转到下一行条件。
The loop seems to be fine (i might be wrong) but when it comes to the autofilter, it does not find any cells. The string variable will not pass to the filter criteria array correctly (if i just hardcode the criteria to the filters - everything works).
循环似乎很好（我可能是错的）但是当涉及到自动过滤器时，它没有找到任何单元格。字符串变量不会正确传递给过滤器标准数组（如果我只是将标准硬编码到过滤器 - 一切正常）。
Does my code contain some errors, or is the whole approach flawed?
我的代码是否包含一些错误，或者整个方法是否有缺陷？
Any assistance is appreciated.
任何帮助表示赞赏。
Dim ExcRange As Range
Dim Col As Range
Dim Filter1 As Variant
With RawData.Worksheets(1)
.AutoFilterMode = False
End With
Dim Lastrow3 As Integer
Lastrow3 = RawData.Worksheets(1).Cells(Rows.Count, "O").End(xlUp).Row
Set ExcRange = RawData.Worksheets(1).Range("O11:O" & Lastrow3)
For Each Col In ExcRange
If Not Col Is Nothing Then
Filter1 = Split(Col.Value, ",")
With NewBook.Worksheets(1)
.Cells.AutoFilter Field:=1, Criteria1:=Array(Filter1), Operator:=xlFilterValues, Operator:=xlAnd
End With
Else
End If
Next Col
I have also tried coding the autofilters this way:
我也尝试过以这种方式对自动过滤器进行编码：
.Cells.AutoFilter Field:=5, Criteria1:=Filter1, Operator:=xlFilterValues, Operator:=xlAnd
回答by Kazimierz Jawor
I think you need both- remove quotation marks and make an array as of each cell. You could achieve this by calling both Splitand Replacefunction. Try to do it in this way:
我认为你需要两者 - 删除引号并为每个单元格创建一个数组。您可以通过调用Split和Replace函数来实现这一点。尝试这样做：
Filter1 =Split(Replace(Col.Value, Chr(34), ""), ",")
Filter2 = Split(Replace(Col.Offset(0, 1).Value, Chr(34), ""), ",")
Filter3 = Split(Replace(Col.Offset(0, 2).Value, Chr(34), ""), ",")
and keep filtering in this way ...Criteria1:=Array(Filter1)...
并继续以这种方式过滤 ...Criteria1:=Array(Filter1)...
It works if I made sample test.
如果我进行了示例测试，它会起作用。
