How can I return the row number of a maximum value in a range in VBA?

[dan7055]

I can use the worksheetfunction.max() to get the maximum value in a range, but how can I instead return the row number of said maximum value?

 

[JLGWhiz]

I can use the worksheetfunction.max() to get the maximum value in a range, but how can I instead return the row number of said maximum value?

myval = Application.Max(Range("A1:A10")
Range("A1:A10").Find(myVal, , xlValues).Row

 

[Kenneth Hobson]

If you want a formula, looking in column A: =ROW(INDEX(A2:A8,MATCH(MAX(A2:A8),A2:A8),1))

 

[Snakehips]

Maybe if range is dynamic....

Set Rng = Range("D3:D15")   'or whatever
Mx = WorksheetFunction.Max(Rng)
Rw = WorksheetFunction.Match(Mx, Rng, 0) + Rng.Row - 1

 

[dan7055]

If you want a formula, looking in column A: =ROW(INDEX(A2:A8,MATCH(MAX(A2:A8),A2:A8),1))

Thanks, I actually need it in VBA, but I'll throw a worksheetfunction. in front of it and see if it works.

 

[Kenneth Hobson]

As with any method, error catching should be done. For simplicity, it was not done in our solutions.

While one can do a formula and Evaluate it in VBA, I would just go with the Find method as #2 and #4 posts show.

MsgBox Range("A1:A10").Find(WorksheetFunction.Max(Range("A1:A10"))).Row