CODEWITHSUNDEEP
arraysexcelvbacomparerange
Dennis
Dennis

Reputation: 19

EXCEL VBA compare cell values to an Array

I've used this post as inspiration: How to find if an array contains a string

Hope you guys can help me out:

I want to loop through a column (range) cell by cell in the Excel sheet and lookup if the cell values are within an array which is also being filed from an excel range, but for some reason I'm getting a type mismatch error. I guess, it something to do with the declaration of the array as variant, but changing this to string didn't help either.

Many thanks in advance and any help is much appreciated

Function IsInArray(stringToBeFound, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Sub Check()

Dim cell As Range
Dim arr() As String
arr = Range("I2:I3").Value

For Each cell In Range("D2:D15")

    If IsInArray(CStr(cell.Value), arr) Then
     cell.Interior.Color = RGB(0, 176, 80)
    Else
     cell.Interior.Color = RGB(0, 0, 0)
    End If

Next cell
End Sub

Upvotes: 0

Views: 13454

Answers (1)

JensS
JensS

Reputation: 1151

Filter only works on Strings - not objects; a simple solution would be to use this instead:

Function IsInArray(ByVal VarToBeFound As Variant, ByVal Arr As Variant) As Boolean
    Dim Element As Variant
    For Each Element In Arr
        If Element = VarToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next Element

    IsInArray = False
End Function

Upvotes: 1

Related Questions