Unanswered: VBA: Getting cell address of maximum value in range
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
Being as yiu are using VBA I assume you have not objection to using code, such as
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
I don't normally use 'Application.WorksheetFunction' but it works so why not!