UDF if value is in array - Type Mismatch from a prior answer - IsInArray

Question

How to find if an array contains a string Using the UDF from this answer I am trying to test whether each cell in column C is in the aCheck array. Eventually I would like to cut the row the cell is on (if confirmed to be within the array) and paste that row to a new sheet, but as of right now I receive a type mismatch error or a By Ref error with the UDF at times when I try and debug.

I've modified the function I found in the above answer to be ByVal arr As Range when it originally was arr As Variant

The acheck(aCheck_Row, 1) expression is defined as a string in the locals window and aSelection does reference column C when I view the expression within the locals window, also the Values2 expression does literally list the worksheet values on the worksheet..

Like I've mentioned I'm messed around with ByVal vs ByRef and changing the object in the function from an array to a range.. I've also noted the above answer suggests the answer only works for a 1D array which I believe I have.

How could I test whether a value in Column C is in array acheck(aCheck_Row, 1) or if that cannot be done how can I test if a value in Column C is in range aSelection?

Function IsInArray(stringToBeFound As String, ByVal arr As Range) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

LR = Range("C1500").End(xlUp).Row
ReDim acheck(1 To LR)

For i = 2 To LR
    aCheck_Row = aCheck_Row + 1
    acheck(aCheck_Row, 1) = CStr(cells(i, cA))    
Next i

For Each cell In aSelection
  If IsInArray(cell.Value, aSelection) Then
    cell.Style = "Accent1"
  End If
Next cell

Using Filter() is not a great approach unless you're happy also returning a match on substrings of the array elements. — Tim Williams, Jul 07 '16 at 17:37

arcadeprecinct · Answer 1 · 2016-07-07T17:48:17.317

Filter takes an array as parameter and a Range is not an array. Range.Value produces an arrow filled with the values of the range however it is a two-dimensional array even if the range is not (it becomes a (1 To 1, 1 To n) or (1 To n, 1 To 1) array).

The easiest solution in this case would be to search the range directly

Function IsInRange(stringToBeFound As String, ByVal rng As Range) As Boolean
    Dim r As Range
    Set r = rng.Find(What:=stringToBeFound, _
        MatchCase:=True, _
        LookIn:=xlValues, _
        Lookat:=xlPart) 'partial match to have the same behaviour as the filter version
    If Not r Is Nothing Then IsInRange = True
End Function

edit: you should also set the search parameters to your needs

UDF if value is in array - Type Mismatch from a prior answer - IsInArray

1 Answers1