View Single Post
  #18   Report Post  
Posted to microsoft.public.excel.misc
Jay Somerset Jay Somerset is offline
external usenet poster
 
Posts: 19
Default 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


If the largest value is in the first cell, then "s" will be undefined. Need
to preset it to s=r.Cells(1,1).Address outside the loop. Also, if there are
multiple cells each with the largest values, the subroutine will only
identify the first one encountered.