Excel Table Range into Array and IsInArray function
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.
Code:
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
Re: Excel Table Range into Array and IsInArray function
Kles
Welcome to the Forums :wave:
What line of your code is throwing the error?
Spoo
Re: Excel Table Range into Array and IsInArray function
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
Re: Excel Table Range into Array and IsInArray function
from help
Quote:
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.