Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •