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)))
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…