excel - VBA Cell address of Max()

Question

I have something like

sdMax = WorksheetFunction.Max(Range("D2", Cells(emptyRow, 4)))

to find the maximum number of column D

How do I find the location of this maximum number?

Answer 1

Defined as a user defined function in vba, returning the address as a string

Function AddressOfMax(rng As Range) As String
    AddressOfMax = WorksheetFunction.Index(rng, WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0)).Address

End Function

Or returning a range reference

Function AddressOfMax(rng As Range) As Range
    Set AddressOfMax = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0))

End Function

these functions assume rng is one column wide

These functions can be used in the sheet
eg

=AddressOfMax(C:C)

or in vba
eg

Dim r As Range
Set r = AddressOfMax(Range("D2", Cells(emptyRow, 4)))