return max. value from a range...

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


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.
Question by:Mike Eghtebas
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 22

Expert Comment

ID: 40011944
Try something like this:

Public Function fnRangeMax(strRangeName As String)

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

LVL 34

Author Comment

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


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

LVL 22

Accepted Solution

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


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
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40011990
Thank you.

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

628 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