Find row number of maximum value
 

Hello,

my task is as follows:

Look for the maximum of the average grades and display it in a message
box by the MsgBox function.

Now that I found the max. value with

Set myRange = Range("E2", ActiveCell)
max = Application.WorksheetFunction.max(myRange)


how do I get the respective row number?

Any help will be appreciated.

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

Find row number of maximum value
 

Try something like the following:

Dim DataRng As Range
Dim MaxVal As Variant
Dim MaxRow As Long
Set DataRng = Range("A2:A10")
With Application.WorksheetFunction
MaxVal = .Max(DataRng)
MaxRow = DataRng.Row + .Match(MaxVal, DataRng, 0) - 1
MsgBox MaxRow
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"CGeorges " wrote in
message ...
Hello,

my task is as follows:

Look for the maximum of the average grades and display it in a
message
box by the MsgBox function.

Now that I found the max. value with

Set myRange = Range("E2", ActiveCell)
max = Application.WorksheetFunction.max(myRange)


how do I get the respective row number?

Any help will be appreciated.

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

Find row number of maximum value
 

Hello Chip and Auk Ales,

thanks a lot for your help.

Georges


---
Message posted from http://www.ExcelForum.com/