EXCEL VBA compare cell values to an Array

Question

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

JensS · Accepted Answer · 2017-09-05T10:06:55.860

1

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

edited Sep 05 '17 at 10:06

answered Sep 05 '17 at 08:19

JensS

1,151
2
13
20

Hi JensS. So i just replaced my "Function IsInArray" with yours and left the Sub Check() as it is, but I'm still getting this "Type mismatch" with an Run-time error 13 ? – Dennis Sep 05 '17 at 09:05
just remove the line Dim arr() As String, then the array is declared as a variant – JensS Sep 05 '17 at 09:24
Ok, now it works further untill the line "If Str(Element) = Str(VarToBeFound) Then" in the function but then i'm again getting the "Type mismatch" , Run-time error 13 – Dennis Sep 05 '17 at 09:49
Well, if your data already contain Strings, you can just write Element = VarToBeFound - but that depends on the data type you are trying to compare. – JensS Sep 05 '17 at 10:02
...and thats exactly what I needed to do!! Now it works perfectly. Super and thank you very much for your quick help, much appriciated . – Dennis Sep 05 '17 at 10:28

EXCEL VBA compare cell values to an Array

1 Answers1