vba code

[Paul]

what am i doing wrong here??

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

MsgBox row

End Sub

 

[Bernie Deitrick]

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").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]

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