PDA

View Full Version : Solved: Charting Macro Problem



peacenik
05-20-2008, 03:21 PM
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
05-21-2008, 05:06 AM
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
05-21-2008, 04:12 PM
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
05-21-2008, 04:18 PM
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
05-22-2008, 03:05 AM
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
05-22-2008, 03:19 AM
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.