Hi all,

I want to find out which index in the array that corresponds to the maximum value in a array.

ff = Application.WorksheetFunction.Max(Hist)

finds the value to which I would like to find the index for.

Hist is an array containing several thousands of numbers. So I would like to avoid looping through the array to save time.

Any ideas out there

Grateful for help


Re: finding index in an array

Andy Pope


You don't show the details of the array Hist but if you can use the Max function maybe you can make use of the Match one to.

Sub Test()
Dim vntMaxValue As Variant
Dim lngMaxIndex As Long
Dim Hist(1 To 5000) As Double
Dim lngIndex As Long

For lngIndex = 1 To 5000
Hist(lngIndex) = Rnd() * 10
Cells(lngIndex, 1) = Hist(lngIndex)

vntMaxValue = Application.WorksheetFunction.Max(Hist)
lngMaxIndex = Application.WorksheetFunction.Match(vntMaxValue, Hist, 0)

MsgBox "Max Item is " & vntMaxValue & vbLf & "Index is " & lngMaxIndex

End Sub

Re: finding index in an array


Thanks Andy!