Home |
Search |
Today's Posts |
#4
![]()
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
![]()
Hi Harlan:
Your suggestion to use MAX and then loop for it is very good. I tried the following: Sub findmax() Dim s As String m = Application.Max(Selection) s = Selection.Find(What:=m, After:=ActiveCell).Address MsgBox (s) End Sub That is using Find in place of a loop. This works. I then tried to make a function: Function superfoo(r As Range) As String Dim s As String m = Application.Max(r) superfoo = r.Find(What:=m, After:=ActiveCell).Address End Function This throws a #VALUE! error. What am I doing wrong? -- Gary''s Student "Harlan Grove" wrote: Gary''s Student wrote... Sub findmax() If there were OBVIOUSLY something for which a FUNCTION would be most appropriate, this is it! Dim r As Range Set r = Selection v = r.Cells(1, 1).Value For Each r In Selection If r.Value v Then v = r.Value s = r.Address End If Next MsgBox ("maximum value " & v & " found in cell " & s) End Sub This can easily be converted into a function as well. Should NEVER have been offerred as a sub. Parametrizing the function isn't completely trivial, and deciding what to return (range object pointing to the cell containing the max value, a string containing the cell's address or an array of row and column indices) is worthy of some thought. Also inefficient. No reason to search through the entire range. WorksheetFunction.Max will return the max value more quickly than iterating through the range in VBA, then exit when the first instance of the max value is found. The following returns the address of that cell. Function foo(rng As Range) As String Dim c As Range, x As Double x = Application.WorksheetFunction.Max(rng) For Each c In rng If c = x Then foo = c.Address(0, 0) Exit Function End If Next c End Function There's also the question of which direction to search. That is, if the range contained 1 1 1 9 1 2 1 3 1 1 3 1 2 2 3 9 3 1 1 2 2 1 1 3 2 should the 9 in the top row or the 9 in the leftmost column be considered the first max value found? Finally, no need for udfs. This can be done with an array formula. If the range were named D, =ADDRESS(INT(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D) ))/1000), MOD(MIN(IF(D=MAX(D),ROW(D)*1000+COLUMN(D))),1000)) would return the address of topmost then leftmost max value and =ADDRESS(MOD(MIN(IF(D=MAX(D),ROW(D)+1000*COLUMN(D) )),1000), INT(MIN(IF(D=MAX(D),ROW(D)+1000*COLUMN(D)))/1000)) would return the address of the leftmost then topmost max value. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Finding Location of Maximum Value in 2D Array | Excel Discussion (Misc queries) | |||
How to know location of cell found with MIN/MAX? | Excel Discussion (Misc queries) | |||
finding cell location | Excel Discussion (Misc queries) | |||
Help creating a report from a data table | Excel Discussion (Misc queries) | |||
Question to Bob Phillips (or whoever...) | Excel Worksheet Functions |