Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code
what am i doing wrong here??
Sub testtt() row = Sheets("DATA").WorksheetFunction.max(Range ("a10:a20")) MsgBox row End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
option buttons run Click code when value is changed via VBA code | Excel Programming |