Dynamically add series to a chart

Question

I'm trying to create an excel which, using a couple of data validation toggle lists and a button, allows a user to display a specific plot for a specific stage of a model build. To do this, I need to be able to add series dynamically to a chart. I have found lots of material about this online, but in spite of my best efforts, I can't get my script to work. The lines within the if condition always seem to generate the following error "run time error '1004': application-defined or object-defined error". Any help would be greatly appreciated.

Sub UpdateChart()

'declaring variables'
Dim chrt As ChartObject
Dim chrtsercoll As SeriesCollection
Dim chtser As Series

'create the series collection from the chart'
Set chrt = ActiveSheet.ChartObjects(1)
'Get the series collection from the chart'
Set chrtsercoll = chrt.Chart.SeriesCollection

'delete all existing series in chart'
For Each chtser In chrtsercoll
        chtser.Delete
    Next chtser

'set up series in case of residual plot'
If Range("C21").Value = "residual series" Then
    With chrtsercoll.NewSeries()
    .Name = "=" & ActiveSheet.Name & "!B15"
    .Values = "=" & ActiveSheet.Name & "!" & Evaluate(ActiveSheet.Names("RSr").Value)
    End With
    ActiveSheet.ChartObjects(1).Chart.ChartType = xlLine
End If

End Sub   

Answer 1

Something like this should work.

• always qualify range references etc with a worksheet
• use quotes around sheet names in range references in case the sheet name has spaces
• you're not assigning anything to the series XValues
• unclear if there's any issue with Evaluate(ActiveSheet.Names("RSr").Value) - maybe you can explain what you're doing there?

Sub UpdateChart()

    Dim ws As Worksheet

    Set ws = ActiveSheet
    With ws.ChartObjects(1).Chart
        'remove existing data
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        'set up series in case of residual plot'
        If ws.Range("C21").Value = "residual series" Then
            With .SeriesCollection.NewSeries
                .Name = "='" & ws.Name & "'!B15" 'use quotes in case name has spaces
                .XValues = 1 'you need to add something here....
                .Values = "='" & ws.Name & "'!" & Evaluate(ActiveSheet.Names("RSr").Value)
            End With
            .ChartType = xlLine
        End If
    End With

End Sub