CODEWITHSUNDEEP
arraysexcelvbaloops
Jorisdrees
Jorisdrees

Reputation: 75

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

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!

Upvotes: 3

Views: 9334

Answers (2)

JvdV
JvdV

Reputation: 75850

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.

Upvotes: 1

JohnyL
JohnyL

Reputation: 7142

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

Upvotes: 3

Related Questions