VBA Tip: Position in an array
To find the position of a value in an array, copy this function into a module:
Function array_pos(my_array, my_value)
'https//www.excel-pratique.com/en/vba_tricks/position-in-array-function
array_pos = -1
For ii = LBound(my_array) To UBound(my_array)
If my_array(ii) = my_value Then 'If value found
array_pos = ii
Exit For
End If
Next
End Function
The array_pos function will then return the position of the first value found in the array (or -1 if the value was not found in the array).
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 then display its position in a MsgBox.
Sub test()
test_array = Array(23, 67, 38, 17, 854, 9, 92)
test_value = 9
MsgBox array_pos(test_array, test_value)
End Sub