PDA

View Full Version : Chart series don't extend



JackkG
05-31-2019, 11:16 AM
Hi,

I'm trying to extend the chart according to new added values. Here is the sample code i have recorded through a macro, but it doesn't seem to work when i run the code? Any help or tweak to the code is much appreciated. Thanks in advance!


Sub Chartext()

If ThisWorkbook.Worksheets("Sum").Range("A2").Value = 5 Then
ThisWorkbook.Worksheets("Schart").ChartObjects("Chart 1").Activate
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).XValues = "=Schart!$L$345:$Z$345"
ActiveChart.FullSeriesCollection(1).Values = "=Schart!$L$346:$Z$346"
ActiveChart.FullSeriesCollection(2).Values = "=Schart!$L$348:$Z$348"
ActiveChart.FullSeriesCollection(3).Values = "=Schart!$L$356:$Z$356"
ActiveChart.FullSeriesCollection(4).Values = "=Schart!$L$332:$Z$332"
ActiveChart.FullSeriesCollection(5).Values = "=Schart!$L$350:$Z$350"
End If
End Sub

The same has been posted here:
https://www.excelforum.com/excel-charting-and-pivots/1277898-chart-series-dont-extend.html#post5130022

Aussiebear
05-31-2019, 06:42 PM
Aren't you already receiving assistance on this subject in another forum?

p45cal
06-01-2019, 08:00 AM
I think you may need to set the XValues for all the series:
With ActiveSheet.ChartObjects("Chart 1").Chart
.FullSeriesCollection(1).Values = "=Sheet1!$I$22:$U$22" 'I've used columns I:U - adjust yourself.
.FullSeriesCollection(2).Values = "=Sheet1!$I$23:$U$23"
.FullSeriesCollection(3).Values = "=Sheet1!$I$24:$U$24"
.FullSeriesCollection(4).Values = "=Sheet1!$I$25:$U$25"
.FullSeriesCollection(5).Values = "=Sheet1!$I$26:$U$26"
.FullSeriesCollection(1).XValues = "=Sheet1!$I$21:$U$21"
.FullSeriesCollection(2).XValues = "=Sheet1!$I$21:$U$21"
.FullSeriesCollection(3).XValues = "=Sheet1!$I$21:$U$21"
.FullSeriesCollection(4).XValues = "=Sheet1!$I$21:$U$21"
.FullSeriesCollection(5).XValues = "=Sheet1!$I$21:$U$21"
End With

Alternatives:
With ActiveSheet.ChartObjects("Chart 1").Chart
.FullSeriesCollection(1).Values = Range("$D$22:$X$22")
.FullSeriesCollection(2).Values = Range("$D$23:$X$23")
.FullSeriesCollection(3).Values = Range("$D$24:$X$24")
.FullSeriesCollection(4).Values = Range("$D$25:$X$25")
.FullSeriesCollection(5).Values = Range("$D$26:$X$26")
.FullSeriesCollection(1).XValues = Range("$D$21:$X$21")
.FullSeriesCollection(2).XValues = Range("$D$21:$X$21")
.FullSeriesCollection(3).XValues = Range("$D$21:$X$21")
.FullSeriesCollection(4).XValues = Range("$D$21:$X$21")
.FullSeriesCollection(5).XValues = Range("$D$21:$X$21")
End With
or:
Set SceDataRng = Range("D21").CurrentRegion
With ActiveSheet.ChartObjects("Chart 1").Chart
.FullSeriesCollection(1).XValues = SceDataRng.Rows(1)
.FullSeriesCollection(2).XValues = SceDataRng.Rows(1)
.FullSeriesCollection(3).XValues = SceDataRng.Rows(1)
.FullSeriesCollection(4).XValues = SceDataRng.Rows(1)
.FullSeriesCollection(5).XValues = SceDataRng.Rows(1)
.FullSeriesCollection(1).Values = SceDataRng.Rows(2)
.FullSeriesCollection(2).Values = SceDataRng.Rows(3)
.FullSeriesCollection(3).Values = SceDataRng.Rows(4)
.FullSeriesCollection(4).Values = SceDataRng.Rows(5)
.FullSeriesCollection(5).Values = SceDataRng.Rows(6)
End With
or:
Set SceDataRng = Range("D21").CurrentRegion
With ActiveSheet.ChartObjects("Chart 1").Chart
For i = 1 To 5
.FullSeriesCollection(i).XValues = SceDataRng.Rows(1)
.FullSeriesCollection(i).Values = SceDataRng.Rows(i + 1)
Next i
End With
(using the attachment at ExcelForum)

JackkG
06-03-2019, 12:35 PM
Thanks, p45cal!!! It worked perfectly fine. Thanks a lot for your help!!