Excel Table Range into Array and IsInArray function

[Kles]

Hello,

I am a first time poster but a longtime admirer of the work and help provided on this forum.

I have been banging my head on this and finally decided that I need to get some help. I am getting a type-mismatch error and I am not using Option Explicit. r = 19 so there are not many rows.

Sub TestArray()

Dim r As Integer

r = Range("ObjCode[[#All],[Cleaning]]").Rows.Count

ReDim aFUNC(1 To r, 1 To 1) As Variant

aFUNC = Range("ObjCode[[#All],[Cleaning]]")

Debug.Print aFUNC(11, 1)

If IsInArray(Range("G8").Value, aFUNC) Then
    MsgBox "Yeah!"
End If


End Sub

Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean

    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)

End Function

Any help is greatly appreciated

Kles

[Spoo]

Kles

Welcome to the Forums
What line of your code is throwing the error?

Spoo

[Kles]

The error occurs on:

IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)

I think it is because arr is a one-dimensional array. I am not sure how to use the filter function on a 2-dimensional array. Thanks

[westconn1]

from help

Remarks

If no matches of match are found within sourcearray, Filter returns an empty array. An error occurs if sourcearray is Null or is not a one-dimensional array.

The array returned by the Filter function contains only enough elements to contain the number of matched items.