VBA Find if an array (made up of range) contains a specific value-CodePudding

I'm trying to write a code which takes a specific predefined, hardcoded value and a range made up of values from 3rd to last column with value (just one row) and sees if my array contains that specific item. The code I have:

Dim LastColumn As Long
LastColumn = Cells(Cells.Find("Parameters", lookat:=xlWhole).Row, Columns.Count).End(xlToLeft).Column

Dim Environment
Environment = ThisWorkbook.Worksheets("Specification").Range(Cells(Cells.Find("Environment").Row, 3), Cells(Cells.Find("Environment").Row, LastColumn)).Value

If ItemIsInArray(Environment , "SKIN") Then
'do stuff
End if

And the function

Function ItemIsInArray(arr As Variant, arrX As Variant) As Boolean
   
'Declare variables
Dim i As Long, j As Long, boolFound As Boolean, mtch

'Main function
If Not IsArray(arrX) Then
    For j = LBound(arr) To UBound(arr)
        If CStr(arr(j)) = CStr(arrX) Then ItemIsInArray = True: Exit For
    Next j
    Exit Function
End If
   
For i = LBound(arrX) To UBound(arrX, 2)
    For j = LBound(arr) To UBound(arr)
        If CStr(arr(j)) = CStr(arrX(1, i)) Then
            boolFound = True: Exit For
        End If
    Next j
        
If boolFound Then ItemIsInArray = True: Exit Function
    boolFound = False
Next i

ItemIsInArray = False

End Function

Problem is that it returns "subscript out of range" on "CStr(arr(j))" in "If CStr(arr(j)) = CStr(arrX) Then ItemIsInArray = True: Exit For".

I guess the problem is the way how I convert the range to an array, but I can't seem to figure it out. Could someone help me solve this?

CodePudding user response：

Your array is 2-dimensional and you're only passing a single dimension in your indexer: arr(j).

You need to loop over both dimensions and use the LBound(array, dimension) and UBound(array, dimension) overloads.

Something like the following:

For i = LBound(arr, 1) To UBound(arr, 1)
    For j = LBound(arr, 2, To UBound(arr, 2)
        If CStr(arr(i, j)) = CStr(arrX) Then ItemIsInArray = True: Exit For
    Next j
Next i