Return Index of an Element in an Array Excel VBA

[ForumBot]

I have an array prLst that is a list of integers. The integers are not sorted, because their position in the array represents a particular column on a spreadsheet. I want to know how I find a particular integer in the array, and return its index.

There does not seem to be any resource on showing me how without turning the array into a range on the worksheet. This seems a bit complicated. Is this just not possible with VBA?

[ForumBot]

```
Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
Msgbox val & " is at position " & pos
else
Msgbox val & " not found!"
end if

```

Updated to show using Match (with .Index) to find a value in a dimension of a two-dimensional array:

```
Dim arr(1 To 10, 1 To 2)
Dim x

For x = 1 To 10
arr(x, 1) = x
arr(x, 2) = 11 - x
Next x

Debug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)
Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)

```

EDIT: it's worth illustrating here what @ARich pointed out in the comments - that using `Index()` to slice an array has horrible performance if you're doing it in a loop.

In testing (code below) the Index() approach is almost 2000-fold slower than using a nested loop.

```
Sub PerfTest()

Const VAL_TO_FIND As String = "R1800:C8"
Dim a(1 To 2000, 1 To 10)
Dim r As Long, c As Long, t

For r = 1 To 2000
For c = 1 To 10
a(r, c) = "R" & r & ":C" & c
Next c
Next r

t = Timer
Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t
' >> 0.00781 sec

t = Timer
Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t
' >> 14.18 sec

End Sub

Function FindLoop(arr, val) As Boolean
Dim r As Long, c As Long
For r = 1 To UBound(arr, 1)
For c = 1 To UBound(arr, 2)
If arr(r, c) = val Then
FindLoop = True
Exit Function
End If
Next c
Next r
End Function

Function FindIndex(arr, val)
Dim r As Long
For r = 1 To UBound(arr, 1)
If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then
FindIndex = True
Exit Function
End If
Next r
End Function

```