VBA How to check if an Array contains a string in a Range

Question

I'm trying to write a little loop to check if a selected range contains any of the values in the Array.

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

I think I'm making the error by using Cells(i,2), it says the Types don't match. I've been looking at it for so long I think I'm missing something obvious.

Any help or feedback would be appreciated!

You cannot compare an array to anything. Use two nested loops. — GSerg
– GSerg, Commented Jan 9, 2020 at 17:02
You have to specify the specific element of the array in the loop, but use MATCH to obviate need for a loop. — SJR
– SJR, Commented Jan 9, 2020 at 17:21

JohnyL · Accepted Answer · 2020-01-09 18:50:05Z

3

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

edited Jan 9, 2020 at 18:50

answered Jan 9, 2020 at 17:56

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JvdV Over a year ago

This can be done without any loop at all, if OP just wants to know if any value in DirArray does exist in their B column equivalent. Either way. In your case you could also do: If Application.Count(Application.Match(.Cells(i,2), DirArray, 0)) > 0 Then MsgBox "it contains the value". This would remove the need for an extra IsError check. +1

JohnyL Over a year ago

@JvdV I guess OP needs to check all values in column B against array values.

JvdV Over a year ago

Yes, I meant that can be done too, without any loop, as long OP is just interested in the fact if any is present.

GSerg Over a year ago

@JvdV You still have two loops, one visible, where you loop the i, and one hidden that is inside Application.Match. You can't call Application.Count(Application.Match()) either as Application.Match returns a single number.

JvdV Over a year ago

@GSerg, Application.Match can return an array of results. I've put down an answer below to demonstrate this.

JvdV · Accepted Answer · 2020-01-10 09:10:36Z

Just for demonstration practices, I wanted to show you wouldn't need any (visible) loop to compare two 1D-arrays to return if any of the elements in one array is found in the other array.

To do so we can use the following code:

Sub Test()

Dim arr1 As Variant: arr1 = Array("A", "B", "C", "D")
Dim arr2 As Variant: arr2 = Array("D", "E", "B")

With Application
    If .Count(.Match(arr2, arr1, 0)) > 0 Then
        MsgBox "It contains values from arr1"
    Else
        MsgBox "It does not contain values from arr1"
    End If
End With

End Sub

What does this actually do? Application.Match is able to compare two arrays, so in this case effectively you could think of:

.Match({"D", "E", "B"}, {"A", "B", "C", "D"}, 0)

It will compare each element in the first array against all elements in the second array, and most importantly it will return an array on it's own with the results:

Results = {4, Error 2042, 2}

As @JohnyL also explained, using Application.Match will not raise an run-time error when values are not found, it will continue and will put non-found matches in the array itself, showing an error in the results instead.

Now to check if there are any result we would need Application.Count to return the number of numeric values withing the resulting array.

.Count({4, Error 2042, 2})

In this case the result will be 2, telling us (higher than zero) that there are two values that have got a match.

_{How would this help OP?}

In his case we would need one more function to return two 1D-arrays directly from the Range objects. Op seems to compare Range("A1:A311") against Range("B1:B311") so we could try the below:

Sub Test2()

Dim arr1 As Variant: arr1 = Sheets("Blad1").Range("A1:B311").Value

With Application
    If .Count(.Match(.Index(arr1, 0, 1), .Index(arr1, 0, 2), 0)) > 0 Then
        MsgBox "It contains values from arr1"
    Else
        MsgBox "It does not contain values from arr1"
    End If
End With

End Sub

The only extra method I used was Application.Index to slice two 1D-arrays directly from the full 2D-array.

Another technique would be to use Application.Transpose if both A and B column would be of different size. You would pull them into a seperate variant variable and Transpose them once into a 1D-array.

Fine demonstration of not so well known possibilities of the Application.Match() function :+)

Collectives™ on Stack Overflow

VBA How to check if an Array contains a string in a Range

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related