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)))

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



"Adam" wrote in message
...
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)))

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

--
regards,
Tom Ogilvy


"Adam" wrote in message
...
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)))