Solved: Unable to set the property of the Series Class

[bhallissey]

I want to use VBA code to dynamically set the values in an embedded line chart. I have tried several approaches and can not get the series to change.

Approach #1
Code to change an existing line series produces Run-time error '1004': Unable to change the Values property of the Series Class

[VBA]
objSht.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).Values = resSht.Range("d4:bk4")
[/VBA]
Approach #2
Code to add a new series and set the series label produces Run-time error '1004': Unable to set the Name property of the Series Class

[VBA]
objSht.ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add Source:=resSht.Range("$d$" & CStr(resRow) & ":$bk$" & CStr(resRow)), SeriesLabels:=False, Categorylabels:=False
ActiveChart.Refresh
ActiveChart.SeriesCollection(1).Name = "=""abc"""

With Approach #2, I also get Run-time error '1004': Delete method of Series class failed.
objSht.ChartObjects(1).Activate
While ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(1).Delete
Wend
[/VBA]
I am at a complete loss! Please help.

[Bob Phillips]

[vba]

objSht.ChartObjects(1).Chart.SeriesCollection(1).Values = resSht.Range("d4:bk4")
[/vba]

[Andy Pope]

Do you have missing or #N/A values in the cells you are trying to link to or that the series is already referencing?

If so you will need to change the line series to a column series for the duration of the range assignment. Column charts are more forgiving when assigning incomplete data.

[bhallissey]

After hours and hours of searching manuals and seeking advice, it turns out this was exactly my problem. In my situation, the easiest solution was to set the series values to a constant value. Once the cells were re-calculated, I set the line series values back to the appropriate ranges. Thank you for your help!