Find row number of maximum value

  • Thread starter Thread starter CGeorges
  • Start date Start date
C

CGeorges

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
 
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,

Sub RowNr()
Set myRange = Range("E2", ActiveCell)
Max = Application.WorksheetFunction.Max(myRange)
For Each C In myRange
If C.Value = Max Then
RowNrMax = C.Row
Exit For
End If
Next
End Sub

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top