如何将标准从字符串数组传输到自动过滤器。 VBA Excel [英] How to transfer criteria from string array to autofilter. VBA Excel
本文介绍了如何将标准从字符串数组传输到自动过滤器。 VBA Excel的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
crit(21)=audi,mercedes
pre>
Cells.Find(What:=Film,After:= ActiveCell,LookIn:= xlFormulas,LookAt:= _
xlPart,SearchOrder:= xlByRows,SearchDirection:= xlNext,MatchCase:= False _
,SearchFormat:= False).Activate
ActiveCell.Select
Set zasieg = Range(ActiveCell,ActiveCell.Offset(0,15))
Selection.AutoFilter
ActiveSheet.Range($ A $ 1:$ P $ 200000)。AutoFilter字段:= 14,Criteria1:= Array(crit(21)),Operator:= xlFilterValues
这部分代码在循环中工作,一些crit(i)有5个元素。
解决方案将数组从字符串中使用,然后使用数组:
Sub dural()
Dim MyString As String,r As Range
MyString =Larry,Moe,Curly,Shepp
ary = Split(MyString,,)
设置r =范围(A1:A14)
$ r
.AutoFilter
.AutoFilter字段:= 1,Criteria1:=(ary),运算符:= xlFilterValues
结束
End Sub
编辑#1:
这是替代代码(不应该使用)
Sub DontUseThisCode()
Dim MyString As String,r As Range
Dim ary(0 To 3)As String
ary(0)=Larry
ary(1)=Moe
ary(2)=Curly
ary(3)=Shepp
设置r =范围(A1:A14)
用r
。 AutoFilter
.AutoFilter字段:= 1,Criteria1:=(ary),运算符:= xlFilterValues
结束
End Sub
I tried in many ways but none of them worked and what i'm trying to do is to transfer data from string array to autofilter criteria. Part of my code:
crit(21) = """audi"", ""mercedes""" Cells.Find(What:="Film", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Select Set zasieg = Range(ActiveCell, ActiveCell.Offset(0, 15)) Selection.AutoFilter ActiveSheet.Range("$A$1:$P$200000").AutoFilter Field:=14, Criteria1:=Array(crit(21)), Operator:=xlFilterValues
This part of code works in the loop and some crit(i) have 5 elements.
解决方案Make an array out of the string and use the array:
Sub dural() Dim MyString As String, r As Range MyString = "Larry,Moe,Curly,Shepp" ary = Split(MyString, ",") Set r = Range("A1:A14") With r .AutoFilter .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues End With End Sub
EDIT#1:
Here is the alternative code (which you should not use)
Sub DontUseThisCode() Dim MyString As String, r As Range Dim ary(0 To 3) As String ary(0) = "Larry" ary(1) = "Moe" ary(2) = "Curly" ary(3) = "Shepp" Set r = Range("A1:A14") With r .AutoFilter .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues End With End Sub
这篇关于如何将标准从字符串数组传输到自动过滤器。 VBA Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文