Application-defined or Object defined error when adding a new series to a chart (VBA)

Question

I'm trying to dynamically add new chart series to a single chart, from a single table of data on that page. However, during the .XValues command, I keep getting

Runtime error '1004': Application-defined or object-defined error.

I'm using the following code:

Sub addseries()

Dim endpt1 As Range
Dim endpt2 As Range
Dim Address1 As Range
Dim Address2 As Range

For x = 2 To (Sheets.Count - 1)
Cells(1, 2 * x - 1).Select
Selection.End(xlDown).Select
Set endpt1 = ActiveCell
Cells(1, 2 * x).Select
Selection.End(xlDown).Select
Set endpt2 = ActiveCell
Range(Cells(2, 2 * x - 1), endpt1).Select
Set Address1 = Selection
Range(Cells(2, 2 * x), endpt2).Select
Set Address2 = Selection

Debug.Print ("Last Row1: " & endpt1.Address & " Last Row2: " & endpt2.Address)
Debug.Print "x Range: " & Address1.Address
Debug.Print "Value Range: " & Address2.Address
'Add new series
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
'Set ranges for new series
ActiveChart.FullSeriesCollection(x).Name = ActiveSheet.Cells(1, 2 * x)
ActiveChart.FullSeriesCollection(x).XValues = "='Merged Plot'!Address1.Address()"
ActiveChart.FullSeriesCollection(x).Values = "='Merged Plot'!Address2.Address()"


Next x
End sub

And these are the values that appear in the immediate window:

Last Row1: $C$62 Last Row2: $D$62
x Range: $C$2:$C$62
Value Range: $D$2:$D$62

X starts at 2 so you need to subtract 1 to get the current series e.g. ActiveChart.SeriesCollection(x - 1).Name — QHarr
But what is this? "='Merged Plot'!Address1.Address()" The Address1 bit? — QHarr

QHarr QHarr · Accepted Answer · 2018-03-27T09:45:56

I think something like this. I would make a lot of other changes but the 2 things currently you should be looking at are:

1) X starts at 2 but new series commence at 1 so remove 1 from X e.g. ActiveChart.SeriesCollection(x - 1).Values

2)C̶o̶n̶c̶a̶t̶e̶n̶a̶t̶e̶ ̶t̶h̶e̶ ̶a̶d̶d̶r̶e̶s̶s̶ ̶f̶o̶r̶ ̶s̶o̶u̶r̶c̶e̶ ̶d̶a̶t̶a̶ ̶ ̶e̶.̶g̶.̶ ̶ ̶̶"̶=̶'̶M̶e̶r̶g̶e̶d̶ ̶P̶l̶o̶t̶'̶!̶"̶ ̶&̶ ̶A̶d̶d̶r̶e̶s̶s̶2̶.̶A̶d̶d̶r̶e̶s̶s̶̶ ̶ Set the address part as follows "=" & Address1.Address(False, False, xlA1, xlExternal) .

Thanks to @Shai Rado for notes on using Address1.Address(False, False, xlA1, xlExternal)

Option Explicit

Sub addseries()

    Dim endpt1 As Range
    Dim endpt2 As Range
    Dim Address1 As Range
    Dim Address2 As Range

    Dim x As Long

    For x = 2 To (Sheets.Count - 1)

        With ActiveSheet

            .Cells(1, 2 * x - 1).Select
            Selection.End(xlDown).Select
            Set endpt1 = ActiveCell
            .Cells(1, 2 * x).Select
            Selection.End(xlDown).Select
            Set endpt2 = ActiveCell
            .Range(.Cells(2, 2 * x - 1), endpt1).Select
            Set Address1 = Selection
            .Range(.Cells(2, 2 * x), endpt2).Select
            Set Address2 = Selection

            Debug.Print ("Last Row1: " & endpt1.Address & " Last Row2: " & endpt2.Address)
            Debug.Print "x Range: " & Address1.Address
            Debug.Print "Value Range: " & Address2.Address

            With ActiveSheet.ChartObjects("Chart 1").Chart
             .SeriesCollection.NewSeries
             .SeriesCollection(x - 1).Name = .Cells(1, 2 * x).Value
             .SeriesCollection(x - 1).XValues = "=" & Address1.Address(False, False, xlA1, xlExternal)
            .SeriesCollection(x - 1).Values = "=" & Address2.Address(False, False, xlA1, xlExternal)
           End With

        End With

    Next x
End Sub

Application-defined or Object defined error when adding a new series to a chart (VBA)

2 Answers