VBA Tip: Search in an Array
To check if a value exists in an array, copy this function into a module:
Function inArray(myArray, myValue)
'Source: https://www.excel-pratique.com/en/vba_tricks/search-in-array-function
inArray = False
For i = LBound(myArray) To UBound(myArray)
If myArray(i) = myValue Then 'If value found
inArray = True
Exit For
End If
Next
End Function
The inArray function will return True or False depending on the result.
Here's a simple example that will look for the presence of the value of the testValue variable in the testArray array and display True or False in a MsgBox:
Sub test()
testArray = Array(23, 67, 38, 17, 854, 9, 92)
testValue = 17
MsgBox inArray(testArray, testValue)
End Sub