Row & column number of max value in an array

[maccyg]

This is probably quite a common question but I can't find a suitable answer on the internet.

Every time I create an array of probabilities I need to record the row and column number of the max value. An example of such an array is below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]11[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


My attempt at solving the problem is:


Sub row_number()

Dim arr As Variant
Dim maxval As Single
Dim rownumber As Integer

arr = Range("A1:E5")

maxval = Application.Max(arr)

For i = 1 To 5
For j = 1 To 5
If arr(i, j) = maxval Then
rownumber = arr(i, j).Row
End If
Next j
Next i

Range("F10").Value = maxval

End Sub


Clearly I'm not using the row function properly in the above code (new to VBA), but am I on the right tracks or should this be done another way?

 

[Roderick_E]

i is already the row, you merely need to return rownumber = i colnum = j

 

[Scott Huish]

I wouldn't use a loop:

Sub test()
Dim r As Double, c As Range, d As Range
Set d = Range("A1:E5")
r = Application.WorksheetFunction.Max(d)
Set c = d.Find(r)
If Not c Is Nothing Then Range("F10") = c.Row
End Sub

 

[maccyg]

I wouldn't use a loop:

Sub test()
Dim r As Double, c As Range, d As Range
Set d = Range("A1:E5")
r = Application.WorksheetFunction.Max(d)
Set c = d.Find(r)
If Not c Is Nothing Then Range("F10") = c.Row
End Sub

Thanks for getting back to me. This has worked perfectly for a Range on a Worksheet, however, it hasn't worked so far on a temporary array I create. I construct TempArray myself but never actually paste the array into a worksheet. Is it possible to Set d = TempArray and your example will return the row of the max value in TempArray?

Alternatively I can just paste TempArray into a worksheet and then run your code as that should be fine.