如何查找数组是否包含字符串

Question

我目前正在研究一个Excel宏，我找不到一种方法 if array.contains(mystring)

我写了以下内容，它给了我“Invaild Qualifier”的信息，并在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

选择是一列

有人帮吗？

嗨，JP我尝试了你的建议，并说它需要对象。并突出显示 If IsInArray（cell.Text，Mainfram）然后继承 我的完整代码

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

没关系，我发现那个愚蠢的错误......谢谢你

Answer 1

使用我的答案中的代码来解决一个非常相似的问题：Sub DoSomething()Dim Mainfram(4) As StringDim cell As Excel.RangeMainfram(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 IfNext cellEnd SubFunction IsInArray(stringToBeFound As String, arr As Variant) As Boolean  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)End Function

Answer 2

使用JOIN和的另一种简单方法INSTRSub 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 celEnd Sub