MAX value in column to use with text for title

[Guest]

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]

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

set myrng = worksheets("somesheetname").range("thedynamicnamerangehere")

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.

 

[Guest]

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

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)

 

[Guest]

No errors to my knowledge. See my code here:

'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

 

[Guest]

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

No errors to my knowledge. See my code here:

'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]

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