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

Question

我目前正在研究一个Excel宏，我找不到一种方法 if array.contains(mystring)我写了以下内容，它给了我“Invaild Qualifier”的信息，并在Mainfram之后突出显示IfDim Mainfram(4) As StringMainfram(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").SelectDim cel As Excel.RangeDim Mainfram(4) As StringMainfram(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 IfNext celEnd SubFunction 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 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

Answer 2

使用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