Change Series Values to Named Range

3
votes

I have a chart in Excel ("Chart 13"), I also have a named range called "nationals".

I'd like to replace one of the chart's series' Y Values with the named range (the chart has three different series, I'm planning to repeat a similar process for all three).

This is what I have been trying with no success:

ActiveWorkbook.Sheets("My_Sheet").ChartObjects("Chart 13").Activate
ActiveChart.SeriesCollection(1).Values = Range("nationals")

I get the following error:

"Run-time error '91': Object variable or With block variable not set"

I feel like I'm close but can't get to the finish line for the life of me! I appreciate any help.

EDIT This is a line chart (with markers) - sorry that wasn't clear before. Simple version of same situation: enter image description here

1
vbaexcel

1 Answers

1
votes

You need to add object to the range like so:

Dim MyChart
Set MyChart = ActiveWorkbook.Sheets("My_Sheet").ChartObjects("Chart 13").Chart

MyChart.SeriesCollection(1).Values = ActiveWorkbook.Sheets("My_Sheet").Range("nationals")

Also, as you note, the ChartObjects().Chart object has the SeriesCollection.