Finding Location of Maximum Value in 2D Array
Gary''s Student wrote:
Hi Major:
Sub findmax()
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.
--
Gary's Student
Thanks Gary's Student,
This is the first time I have used macros in ExCel. My only problem is
that the message box is not large enough for the result. I get
"maximum value 0.00055 found in cell".
Does MsgBox not expand to fit the message?
Thanks,
Peter.
" wrote:
I am using MS Office ExCel 2003(11.6560.6568) SP2 and have a single
sheet with a 2D array of numbers roughly 202x202 in size. Could
someone tell me the quickest way to locate the maximum value in the
array? I tried =WhereMax("Sheet1") but just got #NAME? as the result.
Any assistance would be greatly appreciated,
Peter.
|