vba 如何查找数组是否包含字符串
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/11109832/
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
How to find if an array contains a string
提问by Nicola-V
Possible Duplicate:
How to search for string in MS Access VBA array
I am currently working on an Excel macro, and I could not find a way to do like
if array.contains(mystring)
我目前正在处理 Excel 宏,但找不到类似的方法
if array.contains(mystring)
I wrote the following, and it gives me the message "Invaild Qualifier" and highlights the Mainfram
right after If
我写了下面,它给我的信息“Invaild资格赛”,并强调了Mainfram
之后If
Dim Mainfram(4) As String
Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "fruit"
For Each cel In Selection
If Mainfram.Contains(cel.Text) Then
Row(cel.Row).Style = "Accent1"
End If
Next cel
The selection is a column
选择是一列
Anyone help?
有人帮忙吗?
Hi, JP I tried your suggestion, and it said Object required. And Highlightd the If IsInArray(cell.Text, Mainfram) ThenHeres my full code
嗨,JP 我试过你的建议,它说需要对象。并突出显示 If IsInArray(cell.Text, Mainfram) ThenHeres my full code
Sub changeRowColor()
Columns("B:B").Select
Dim cel As Excel.Range
Dim Mainfram(4) As String
Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "Banana"
For Each cel In Selection
If IsInArray(cell.Value, Mainfram) Then
Rows(cel.Row).Style = "Accent1"
End If
Next cel
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
Nevermind, I found that stupid Error... Thank you anyways
没关系,我发现了那个愚蠢的错误......还是谢谢你
回答by JimmyPena
Using the code from my answerto a very similar question:
Sub DoSomething()
Dim Mainfram(4) As String
Dim cell As Excel.Range
Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "fruit"
For Each cell In Selection
If IsInArray(cell.Value, MainFram) Then
Row(cell.Row).Style = "Accent1"
End If
Next cell
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
回答by Siddharth Rout
Another simple way using JOIN
and INSTR
使用JOIN
和的另一种简单方法INSTR
Sub Sample()
Dim Mainfram(4) As String, strg As String
Dim cel As Range
Dim Delim As String
Delim = "#"
Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "fruit"
strg = Join(Mainfram, Delim)
strg = Delim & strg
For Each cel In Selection
If InStr(1, strg, Delim & cel.Value & Delim, vbTextCompare) Then _
Rows(cel.Row).Style = "Accent1"
Next cel
End Sub
回答by EkoostikMartin
Use the Filter() method - http://msdn.microsoft.com/en-us/library/aa164525(v=office.10).aspx
使用 Filter() 方法 - http://msdn.microsoft.com/en-us/library/aa164525(v=office.10).aspx
回答by Jon Egerton
I'm afraid I don't think there's a shortcut to do this - if only someone would write a linq wrapper for VB6!
恐怕我不认为有捷径可以做到这一点 - 如果有人会为 VB6 编写一个 linq 包装器就好了!
You could write a function that does it by looping through the array and checking each entry - I don't think you'll get cleaner than that.
您可以编写一个函数,通过遍历数组并检查每个条目来执行此操作 - 我认为您不会比这更简洁。
There's an example article that provides some details here: http://www.vb6.us/tutorials/searching-arrays-visual-basic-6
有一篇示例文章在这里提供了一些详细信息:http: //www.vb6.us/tutorials/searching-arrays-visual-basic-6