Range of Worksheet Function Max

[Guest]

I am using the following code to find the max value within a group of cells.
I would like for a variable to also be set as the range of that value. How
do I do that? Attached is my code and what I have tried. Thank you very
much.


wks_W.Range("DN" & m).Value = WorksheetFunction.Max(wks_W.Range("CM" & m &
":CO" & (n - 1)))

wks_W.Range("DO" & m).Value = WorksheetFunction.Max.Range(wks_W.Range("CM" &
m & ":CO" & (n - 1)))

 

[Chip Pearson]

Adam,

Try code similar to the following:

Dim MaxVal As Double
Dim MaxRng As Range
MaxVal = Application.WorksheetFunction.Max(Range("A1:A10"))
Set MaxRng = Range("A1").Offset( _
Application.WorksheetFunction.Match(MaxVal, Range("A1:A10"),
0) - 1)
Debug.Print MaxVal
Debug.Print MaxRng.Address


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Dim rng as range, rng1 as Range
Dim maxVal as Variant
set rng = wks_W.Range("CM" & m & ":CO" & (n - 1))

maxVal = WorksheetFunction.Max(rng)

wks_W.Range("DN" & m).Value = maxVal
set rng1 = rng.Find(maxVal)
wks_W.Range("DO" & m).Value = rng1.Address