Clash Royale CLAN TAG#URR8PPP

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

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


2 Answers
2

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

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) .

̶"̶=̶'̶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̶

"=" & Address1.Address(False, False, xlA1, xlExternal)

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

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


Address1.Address(False, False, xlA1, xlExternal)

xlExternal

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection.NewSeries

"=" & Address1.Address(False, False, xlA1, xlExternal) did the trick!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.