VBA Tip: Search in an array
To check if a value exists in an array, copy this function into a module:
Function in_array(my_array, my_value)
'https://www.excel-pratique.com/en/vba_tricks/search-in-array-function
in_array = False
For i = LBound(my_array) To UBound(my_array)
If my_array(i) = my_value Then 'If value found
in_array = True
Exit For
End If
Next
End Function
The in_array 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 "test_value" variable in the "test_array" array and display True or False in a MsgBox:
Sub test()
test_array = Array(23, 67, 38, 17, 854, 9, 92)
test_value = 17
MsgBox in_array(test_array, test_value)
End Sub