Skip if variable not determined

B

Bruce

I have the following function that I use to adjust the scale on my y-axis.
My sheet has around 12 charts that are updated, but sometimes they might not
contain data causing the macro to holt on the GetChartMin Function.

How can I Skip the yAxis loop when GetChartMin cannot be determined.

Regards,

Bruce

Private Function yAxis()
arrChart = Array("Summary")
For Each a In arrChart
For Each Chart In Sheets(a).ChartObjects()
Chart.Activate
High = GetChartMax(ActiveChart, 1) * 1.01
low = GetChartMin(ActiveChart, 1) * 0.995
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = low
.MaximumScale = High
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = 0
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Next Chart
Next a
Range("A1").Select
End Function

Private Function GetChartMax(MyChart As Chart, SeriesIndex As Integer) As
Double
GetChartMax =
Application.WorksheetFunction.Max(MyChart.SeriesCollection(SeriesIndex).Values)
End Function

Private Function GetChartMin(MyChart As Chart, SeriesIndex As Integer) As
Double
GetChartMin =
Application.WorksheetFunction.Min(MyChart.SeriesCollection(SeriesIndex).Values)
End Function
 
N

Nigel

If I read you right then your statement 'but sometimes they might not
contain data' is the clue. If your data resides in worksheet cells, then I
suggest you test the contents of the data range to see if there is data or
not. You do not state what constitutes missing data if you could be more
explicit then I am sure a solution is possible. For example you could sum
the range and if it is zero, or if the max = min etc.... sometimes these
of course might be valid.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top