Skip if variable not determined

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

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.

--
Cheers
Nigel



"Bruce" <Bruce@discussions.microsoft.com> wrote in message
news:026E2117-18CF-4791-8016-561BE1BA7D8B@microsoft.com...
>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