=max query

Topic

New Reply

=max(range) returns the max value. Fair enough.
But what’s the quickest way to then go to the cell that contains this value? (other than using ctrl F)

ps andrew- the missing $ did the trick…

Reply | Quote

Replies

You could use a VBA procedure like this:

Public Sub GoToMax()
Dim dMax As Double
Dim oCell As Range, oMaxCell As Range
    For Each oCell In Selection
        If IsNumeric(oCell.Value) Then
            If oMaxCell Is Nothing Then
                dMax = oCell.Value
                Set oMaxCell = oCell
            Else
                If oCell.Value > dMax Then
                    dMax = oCell.Value
                    Set oMaxCell = oCell
                End If
            End If
        End If
    Next oCell
    If Not oMaxCell Is Nothing Then oMaxCell.Select
End Sub

Select the cells you want to go to the max in and run the macro.

Reply | Quote

Couldn’t you use Match(Max(range) to find it’s location then index it? Something like
Index(Match(Max(range))) to get what is there – not a good example but an idea?

Bob

Reply | Quote