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-05 10:06:55Z

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 5, 2017 at 10:06

answered Sep 5, 2017 at 8:19

JensS

1,1512 gold badges13 silver badges20 bronze badges

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

5 Comments

Dennis Over a year ago

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 ?

JensS Over a year ago

just remove the line Dim arr() As String, then the array is declared as a variant

Dennis Over a year ago

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

JensS Over a year ago

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.

Dennis Over a year ago

...and thats exactly what I needed to do!! Now it works perfectly. Super and thank you very much for your quick help, much appriciated .

Collectives™ on Stack Overflow

EXCEL VBA compare cell values to an Array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related