Solved

return max. value from a range...

Posted on 2014-04-20
4
185 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 33

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 33

Author Closing Comment

by:Mike Eghtebas
ID: 40011990
Thank you.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now