# VBA Excel - Changing SeriesCollection.Values for Line Chart



## he4dhuntr (Jan 15, 2009)

Hi all,
I'm new to the forums and am having a problem with setting the SeriesCollection Values on my chart.

I have a Bar & Line chart and am having no issue with setting the values for my two bar series, but when it comes to the line series, I get an error message:
"Unable to set the Values property of the Series class"

Here's the code I'm using. Keep in mind that series (1) and (4) are the bar series and series (2) and (3) are the lines. Also, "created", "outstanding", "backlog" and "closed" are all Ranges set in this format:

Set backlog = Sheet5.Range(Cells(r2 + 1, c), Cells(lastUseRow, c))

where r2, lastUseRow and c are dimmed as Long. 
I get the error message at SeriesCollection(2), and all the series already exist in the chart.

Sheet14.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).Values = created
ActiveChart.SeriesCollection(2).Values = outstanding
ActiveChart.SeriesCollection(3).Values = backlog
ActiveChart.SeriesCollection(4).Values = closed

My question can most likely be solved by simply letting me know how to change the Values property of a line chart series. I've tried creating a line chart in an empty Excel Workbook, recorded a macro while changing the series values and got this code:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C1:R7C1"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R2C2:R7C2"
ActiveWindow.Visible = False
Windows("Book1").Activate

Although when I run it, it gives me the same error message as above at SeriesCollection(1). Again, all the series already exist in the chart.

Thanks for any help. I'm stumped 

Much appreciated!
Regards,


----------



## he4dhuntr (Jan 15, 2009)

I fixed the problem by changing the chart type of the series to xlColumnClustered, then changing the Series Values and then changing the chart type back to xlLine.

I guess it's a messy/long way around it, but it works.

Cheers,


----------



## spicylife (Apr 21, 2007)

This article provides illustration of how to change the series order of an Excel Chart using arrays:
http://vbacentral.blogspot.com/2009/11/excel-changing-chart-series-order.html


----------

