Trouble confirming whether a value in one array is in another.

[TheRedCardinal]

This is a continuation of the this thread:

Object Required in my Function to match an array

I have a function (taken from here) to determine if a value appears in an array. The value I'm looking for is the last entry in an array, in box 6. I have coded as follows: If ExportArray(UBound(ExportArray), 6) "" Then If IsInArray(ExportArray(UBound(ExportArray), 6).Value...

www.mrexcel.com

I've managed to get the check working, however I am always getting "False" back from the function even when there is a definite match.

Here is my code:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

CustomerData = WS3.ListObjects(1).DataBodyRange

'Check Customer Entry is correct

If ExportArray(UBound(ExportArray), 6) <> "" Then

    SearchStr = ExportArray(UBound(ExportArray), 6)

    If IsInArray(SearchStr, CustomerData) = False Then
        MsgBox ("Customer Not Found")
    Else
        MsgBox ("Customer Found")
    End If

End If

I must be mixing something up somewhere - what have I done wrong?

Thanks!

 

[aRandomHelper]

I am not sure whether that is possible, but one possible work around could be using .Find, since you're just looking at the table range

Dim f As Range

'Check Customer Entry is correct

If ExportArray(UBound(ExportArray), 6) <> "" Then

    SearchStr = ExportArray(UBound(ExportArray), 6)
    
    Set f = ws3.ListObjects(1).DataBodyRange.Find(SearchStr, lookat:=xlWhole, LookIn:=xlValues)
    If f Is Nothing Then
        MsgBox ("Customer Not Found")
    Else
        MsgBox ("Customer Found")
    End If

End If

 

[TheRedCardinal]

Seemed to work perfectly, thanks,
Mike