VBA: Getting cell address of maximum value in range

scrtchmstj · #1 (**permalink**) 02-10-11, 14:29

I have a range of numbers F9:F33. I'd like to have VBA find the cell address for the cell with the maximum value. I tried using a combination of "Application.WorksheetFunction"s to recreate the excel formula = Cell("address", Index(F9:F33, Match(Max(F9:F33),F9:F33,0))) but apparently Application.WorsheetFunction does not support the Excel "Cell" function. Any ideas how I could go about this? Thanks! Josh

MikeTheBike · #2 (**permalink**) 02-11-11, 08:20

Hi

Being as yiu are using VBA I assume you have not objection to using code, such as

Code:

Function MaxAddress(ByRef ThisRange As Range) As String
    Dim cel As Range
    For Each cel In ThisRange
        If cel = Application.WorksheetFunction.Max(ThisRange) Then MaxAddress = cel.Address
    Next cel
End Function
Sub TestFunction()
    MsgBox MaxAddress(Range("F9:F33"))
End Sub

I don't normally use 'Application.WorksheetFunction' but it works so why not!

HTH

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech