Solved: Charting Macro Problem

[peacenik]

This problem has been driving me crazy for weeks.

I have a macro that defines a series of ranges, then sets the chart data series to those ranges. When I run the macro on one set of data, it runs wit h no problems, but when I run it against a different set of data it fails. This chart is one of eight that I run on this dashboard. There are no issues with any of the other charts.

I would be very greatful if someone can look at it and point out where I am going wrong.

[Andy Pope]

The problem is caused by missing data in your series.

One way of writing the series with missing data is to change the chart type to column first. This modification will at least make you code run.

 Dash.ChartObjects("Wizardlink").Activate
    
    Dim lngChartType     As Long
    
    lngChartType = ActiveChart.SeriesCollection(1).ChartType
    ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
    
    ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(1).XValues = WA.Range("WLMonths")
    ActiveChart.SeriesCollection(1).Values = WA.Range("Mailshots")
    ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(2).Values = WA.Range("BAPs")
    ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(3).Values = WA.Range("OwnRecords")
    ActiveChart.SeriesCollection(4).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(4).Values = WA.Range("Activity")
    
    ActiveChart.SeriesCollection(1).ChartType = lngChartType
    ActiveChart.SeriesCollection(2).ChartType = lngChartType
    ActiveChart.SeriesCollection(3).ChartType = lngChartType
    ActiveChart.SeriesCollection(4).ChartType = lngChartType

[peacenik]

Thanks Andy,

I don't understand why that works, but it does the job. There is a momentary flash when i can see the bar chart but that is bearable.

[Bob Phillips]

Andy, when I looked at this I thougt it might be that too ... but Branch A has missing data for Mar-07 too, so how come that works.

[Andy Pope]

You are right it is more subtle than that.

Branch A has 15 rows of information, including the header.
Branch B has 28 rows.

But the problem is that Branch B data does not start until row 16.
So when swapping between the two there is a stage where the series covers the range AD2:AD15 and this is totally blank.

If you add data to row 15 on sheet Branch B the original code will work.

[Bob Phillips]

Okay, that makes sense now.

My thinking was working towards that (I think!), but it was nowhere near as developed as yours, nor as sure about charts as you are.

Thanks for the clarification.