vba code
 

what am i doing wrong here??

Sub testtt()
row = Sheets("DATA").WorksheetFunction.max(Range
("a10:a20"))

MsgBox row

End Sub

vba code
 

Paul,

what am i doing wrong here??

WorksheetFunction is a property of the Application, not the Worksheet, and
MAX returns the maximum value, not the row where the max is located.

To get the row, you need to use MATCH after finding the MAX, though you
could do it in one step.

Sub testtt2()
myMax = Application.WorksheetFunction.Max(Sheets("DATA").R ange("a10:a20"))
MsgBox "The maximum value is " & myMax
row = Application.WorksheetFunction.Match(myMax,
Sheets("DATA").Range("a10:a20"), False) + 9
MsgBox "The maximum value is found in row " & row
End Sub

HTH,
Bernie
MS Excel MVP

"Paul" wrote in message
...

Sub testtt()
row = Sheets("DATA").WorksheetFunction.max(Range
("a10:a20"))

MsgBox row

End Sub

vba code
 

That wrapped badly. Try this one:

Sub testtt2()
myMax = Application.WorksheetFunction.Max _
(Sheets("DATA").Range("a10:a20"))
MsgBox "The maximum value is " & myMax
Row = Application.WorksheetFunction.Match _
(myMax, Sheets("DATA").Range("a10:a20"), False) + 9
MsgBox "The maximum value is found in row " & Row
End Sub

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Paul,

what am i doing wrong here??

WorksheetFunction is a property of the Application, not the Worksheet, and
MAX returns the maximum value, not the row where the max is located.

To get the row, you need to use MATCH after finding the MAX, though you
could do it in one step.

Sub testtt2()
myMax = Application.WorksheetFunction.Max(Sheets("DATA").R ange("a10:a20"))
MsgBox "The maximum value is " & myMax
row = Application.WorksheetFunction.Match(myMax,
Sheets("DATA").Range("a10:a20"), False) + 9
MsgBox "The maximum value is found in row " & row
End Sub

HTH,
Bernie
MS Excel MVP

"Paul" wrote in message
...

Sub testtt()
row = Sheets("DATA").WorksheetFunction.max(Range
("a10:a20"))

MsgBox row

End Sub