While this is essentially just @Brad's answer again, I thought it might be worth including a slightly modified function which will return the index of the item you're searching for if it exists in the array. If the item is not in the array, it returns -1
instead.
The output of this can be checked just like the "in string" function, If InStr(...) > 0 Then
, so I made a little test function below it as an example.
Option Explicit
Public Function IsInArrayIndex(stringToFind As String, arr As Variant) As Long
IsInArrayIndex = -1
Dim i As Long
For i = LBound(arr, 1) To UBound(arr, 1)
If arr(i) = stringToFind Then
IsInArrayIndex = i
Exit Function
End If
Next i
End Function
Sub test()
Dim fruitArray As Variant
fruitArray = Array("orange", "apple", "banana", "berry")
Dim result As Long
result = IsInArrayIndex("apple", fruitArray)
If result >= 0 Then
Debug.Print chr(34) & fruitArray(result) & chr(34) & " exists in array at index " & result
Else
Debug.Print "does not exist in array"
End If
End Sub
Then I went a little overboard and fleshed out one for two dimensional arrays because when you generate an array based on a range it's generally in this form.
It returns a single dimension variant array with just two values, the two indices of the array used as an input (assuming the value is found). If the value is not found, it returns an array of (-1, -1)
.
Option Explicit
Public Function IsInArray2DIndex(stringToFind As String, arr As Variant) As Variant
IsInArray2DIndex= Array(-1, -1)
Dim i As Long
Dim j As Long
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
If arr(i, j) = stringToFind Then
IsInArray2DIndex= Array(i, j)
Exit Function
End If
Next j
Next i
End Function
Here's a picture of the data that I set up for the test, followed by the test:
Sub test2()
Dim fruitArray2D As Variant
fruitArray2D = sheets("Sheet1").Range("A1:B2").value
Dim result As Variant
result = IsInArray2DIndex("apple", fruitArray2D)
If result(0) >= 0 And result(1) >= 0 Then
Debug.Print chr(34) & fruitArray2D(result(0), result(1)) & chr(34) & " exists in array at row: " & result(0) & ", col: " & result(1)
Else
Debug.Print "does not exist in array"
End If
End Sub