arrays - VBA如何检查数组是否包含范围内的字符串

Question

我正在尝试编写一个小循环来检查所选范围是否包含数组中的任何值。

Sub test()
    Dim DirArray As Variant
    Dim i As Integer

    'define array
    DirArray = Sheets("Blad1").Range("A1:A311").Value

    'Loop trough array
    For i = 1 To UBound(DirArray)
        'Activate the sheet with the Range
        Sheets("Blad1").Activate

        'Go through range of values
        If DirArray = Cells(i, 2) Then
            MsgBox "it contains the value"
        End If
    Next i
End Sub

我认为我使用 Cells(i,2) 会出错，它说类型不匹配。我已经看了很长时间了，我想我错过了一些明显的东西。

任何帮助或反馈将不胜感激!

Answer 1

Sub test()
    Dim i As Integer, z, DirArray As Variant
    With Sheets("Blad1")
        'Define array
        DirArray = .Range("A1:A311").Value
        'Loop trough array
        For i = 1 To UBound(DirArray)
            '// Use Excel's Match function:
            '// if the value of 'z' is not Error, then match is found.
            '// Note that if you use WorksheetFunction.Match instead of
            '// Application.Match and the value won't be found, then
            '// error will be raised, in which case you need to use error handler.
            '// To avoid this ceremony, use Application.Match since it won't raise
            '// error, but the value of 'z' will just contain Error.
            z = Application.Match(.Cells(i, 2), DirArray, 0)
            If Not IsError(z) Then
                MsgBox "it contains the value"
            End If
        Next i
    Next
End Sub