MAX value in column to use with text for title
 

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

MAX value in column to use with text for title
 

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

MAX value in column to use with text for title
 

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

MAX value in column to use with text for title
 

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

MAX value in column to use with text for title
 

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

MAX value in column to use with text for title
 

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

MAX value in column to use with text for title
 

Glad you found the problem--it would have been difficult to debug from a
distance.

This section:

Range("WWRange").Select
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With

could be replaced with:

with worksheets("data sheet").Interior
.ColorIndex = 8
.Pattern = xlSolid
End With

and you'll avoid selecting the cells.

(But I bet selecting the range was how you found the solution!)

rc wrote:

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


--

Dave Peterson