I would like to find the maximum and minimum values (integers) in a column of
dynamic size. Then I will plug these values into the title of a chart, i.e.
"WW17-26 Failure Pareto..."

I can identify the dynamic range but can't get MAX() or LARGE() instructions
to assign the largest value to a variable that I can use in the chart title
in vba. I can find a solution by using =MAX("DynamicRange") inside a cell,
but not sure how to get this into the title of a chart that is created
through vba.

Thanks for any help,
rc

Dim myRng as range
dim myMin as double
dim myMax as Double

set myrng = worksheets("somesheetname").range("thedynamicnamer angehere")

if application.count(myrng) = 0 then
'no numbers, what should happen?
mymin = -999999
mymax = 999999
else
mymin = application.min(myrng)
mymax = application.max(myrng)
end if

Then you can use mymin and mymax in your code.

Dim mytitle as string
.....

mytitle = "ww" & format(mymin,"#,##0.00") & "-" & format(mymax,"#,##0.00") _
& "failure pareto..."

You may not want the formats.

rc wrote:

I would like to find the maximum and minimum values (integers) in a column of
dynamic size. Then I will plug these values into the title of a chart, i.e.
"WW17-26 Failure Pareto..."

I can identify the dynamic range but can't get MAX() or LARGE() instructions
to assign the largest value to a variable that I can use in the chart title
in vba. I can find a solution by using =MAX("DynamicRange") inside a cell,
but not sure how to get this into the title of a chart that is created
through vba.

Thanks for any help,
rc

--

Dave Peterson

I keep getting a type mismatch error...and debugger takes me to mymin =
application.min(myrng) line.

rc


"Dave Peterson" wrote:

Dim myRng as range
dim myMin as double
dim myMax as Double

set myrng = worksheets("somesheetname").range("thedynamicnamer angehere")

if application.count(myrng) = 0 then
'no numbers, what should happen?
mymin = -999999
mymax = 999999
else
mymin = application.min(myrng)
mymax = application.max(myrng)
end if

Then you can use mymin and mymax in your code.

Dim mytitle as string
.....

mytitle = "ww" & format(mymin,"#,##0.00") & "-" & format(mymax,"#,##0.00") _
& "failure pareto..."

You may not want the formats.

rc wrote:

I would like to find the maximum and minimum values (integers) in a column of
dynamic size. Then I will plug these values into the title of a chart, i.e.
"WW17-26 Failure Pareto..."

I can identify the dynamic range but can't get MAX() or LARGE() instructions
to assign the largest value to a variable that I can use in the chart title
in vba. I can find a solution by using =MAX("DynamicRange") inside a cell,
but not sure how to get this into the title of a chart that is created
through vba.

Thanks for any help,
rc

--

Dave Peterson

What do you have in that range?

Do you have any errors in it?

Maybe you can remove the errors -- or change the formula to hide the error:

=if(iserror(yourformula),"",yourformula)



rc wrote:

I keep getting a type mismatch error...and debugger takes me to mymin =
application.min(myrng) line.

rc

"Dave Peterson" wrote:

Dim myRng as range
dim myMin as double
dim myMax as Double

set myrng = worksheets("somesheetname").range("thedynamicnamer angehere")

if application.count(myrng) = 0 then
'no numbers, what should happen?
mymin = -999999
mymax = 999999
else
mymin = application.min(myrng)
mymax = application.max(myrng)
end if

Then you can use mymin and mymax in your code.

Dim mytitle as string
.....

mytitle = "ww" & format(mymin,"#,##0.00") & "-" & format(mymax,"#,##0.00") _
& "failure pareto..."

You may not want the formats.

rc wrote:

I would like to find the maximum and minimum values (integers) in a column of
dynamic size. Then I will plug these values into the title of a chart, i.e.
"WW17-26 Failure Pareto..."

I can identify the dynamic range but can't get MAX() or LARGE() instructions
to assign the largest value to a variable that I can use in the chart title
in vba. I can find a solution by using =MAX("DynamicRange") inside a cell,
but not sure how to get this into the title of a chart that is created
through vba.

Thanks for any help,
rc

--

Dave Peterson


--

Dave Peterson

No errors to my knowledge. See my code he

'create dynamic range to determine Range of WW
Dim WWRng As Range
Dim WWMin As Long
Dim WWMax As Long
Dim WWRange As Range

ActiveWorkbook.Names.Add Name:="WWRange", RefersToR1C1:= _
"=OFFSET('Data Sheet'!R1C1,1,7,CountA(C1),1)"
Range("WWRange").Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With

Set WWRng = Worksheets("Data Sheet").Range("WWRange")
If Application.Count(WWRng) = 0 Then
WWMin = 0
WWMax = 0
Else
WWMin = Application.Min(WWRng)
WWMax = Application.Max(WWRng)
End If


I just added the range selection and fill property to verify correct range
selected and it works fine.

Thanks,
rc



"Dave Peterson" wrote:

What do you have in that range?

Do you have any errors in it?

Maybe you can remove the errors -- or change the formula to hide the error:

=if(iserror(yourformula),"",yourformula)



rc wrote:

I keep getting a type mismatch error...and debugger takes me to mymin =
application.min(myrng) line.

rc

"Dave Peterson" wrote:

Dim myRng as range
dim myMin as double
dim myMax as Double

set myrng = worksheets("somesheetname").range("thedynamicnamer angehere")

if application.count(myrng) = 0 then
'no numbers, what should happen?
mymin = -999999
mymax = 999999
else
mymin = application.min(myrng)
mymax = application.max(myrng)
end if

Then you can use mymin and mymax in your code.

Dim mytitle as string
.....

mytitle = "ww" & format(mymin,"#,##0.00") & "-" & format(mymax,"#,##0.00") _
& "failure pareto..."

You may not want the formats.

rc wrote:

I would like to find the maximum and minimum values (integers) in a column of
dynamic size. Then I will plug these values into the title of a chart, i.e.
"WW17-26 Failure Pareto..."

I can identify the dynamic range but can't get MAX() or LARGE() instructions
to assign the largest value to a variable that I can use in the chart title
in vba. I can find a solution by using =MAX("DynamicRange") inside a cell,
but not sure how to get this into the title of a chart that is created
through vba.

Thanks for any help,
rc

--

Dave Peterson


--

Dave Peterson

I figured out what was happening. My dynamic range was one row too large and
included an erroneous cell. I fixed the range and all is well. Thanks for
your help Dave.

rc


"rc" wrote:

No errors to my knowledge. See my code he

'create dynamic range to determine Range of WW
Dim WWRng As Range
Dim WWMin As Long
Dim WWMax As Long
Dim WWRange As Range

ActiveWorkbook.Names.Add Name:="WWRange", RefersToR1C1:= _
"=OFFSET('Data Sheet'!R1C1,1,7,CountA(C1),1)"
Range("WWRange").Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With

Set WWRng = Worksheets("Data Sheet").Range("WWRange")
If Application.Count(WWRng) = 0 Then
WWMin = 0
WWMax = 0
Else
WWMin = Application.Min(WWRng)
WWMax = Application.Max(WWRng)
End If


I just added the range selection and fill property to verify correct range
selected and it works fine.

Thanks,
rc



"Dave Peterson" wrote:

What do you have in that range?

Do you have any errors in it?

Maybe you can remove the errors -- or change the formula to hide the error:

=if(iserror(yourformula),"",yourformula)



rc wrote:

I keep getting a type mismatch error...and debugger takes me to mymin =
application.min(myrng) line.

rc

"Dave Peterson" wrote:

Dim myRng as range
dim myMin as double
dim myMax as Double

set myrng = worksheets("somesheetname").range("thedynamicnamer angehere")

if application.count(myrng) = 0 then
'no numbers, what should happen?
mymin = -999999
mymax = 999999
else
mymin = application.min(myrng)
mymax = application.max(myrng)
end if

Then you can use mymin and mymax in your code.

Dim mytitle as string
.....

mytitle = "ww" & format(mymin,"#,##0.00") & "-" & format(mymax,"#,##0.00") _
& "failure pareto..."

You may not want the formats.

rc wrote:

I would like to find the maximum and minimum values (integers) in a column of
dynamic size. Then I will plug these values into the title of a chart, i.e.
"WW17-26 Failure Pareto..."

I can identify the dynamic range but can't get MAX() or LARGE() instructions
to assign the largest value to a variable that I can use in the chart title
in vba. I can find a solution by using =MAX("DynamicRange") inside a cell,
but not sure how to get this into the title of a chart that is created
through vba.

Thanks for any help,
rc

--

Dave Peterson


--

Dave Peterson