Solved

return max. value from a range...

Posted on 2014-04-20
4
188 Views
Last Modified: 2014-04-20
There is a named-range (R_01) representing the following cells:

Formulas!$D$1:$AA$2

Question: How can I detect the max. value in this range?

MsgBox fnRangeMax("R_01")

Public Function fnRangeMax(strRangeName As String)

   fnRangeMax = <how to look through this R_01 and return the max. value it has?>
End Function

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40011944
Try something like this:

Public Function fnRangeMax(strRangeName As String)

Set rg = Range("R_01")
 
 fnRangeMax = Application.Max(rg)
     
End Function

Flyster
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40011978
Can I also enter this a formula into a cell?

=Max(Range("R_01"))

I tired this it didn't work. Any idea how this would work if possible at all?

Mike
0
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 40011989
If you want a formula for the cell, use:

=MAX($D$1:$AA$2)

This macro also works:

Sub FindMax()
Dim msg As String

Set rg = Range("R_01")
 
 fnRangeMax = Application.Max(rg)
 msg = MsgBox("The Max Value is " & fnRangeMax, vbInformation)

End Sub
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40011990
Thank you.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question