Change data range in a chart using VBA

Question

It has been hours that I'm struggling with what I think to be a sible problem since Im not familiar at all with Chart object in VBA.

Here's my chart:

What I want to do: Change the data range of the two axis, the problem is that cant figure out a way to edit the series of the chart.

Thanks four your help !

Have you tried the macro recorder?

BigBen
– BigBen

2018-09-20 15:59:18 +00:00
Commented Sep 20, 2018 at 15:59 — BigBen
– BigBen, Commented Sep 20, 2018 at 15:59
You could use dynamic named ranges and no need for VBA.

Darren Bartrup-Cook
– Darren Bartrup-Cook

2018-09-20 16:28:37 +00:00
Commented Sep 20, 2018 at 16:28 — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Sep 20, 2018 at 16:28

Jchang43 · Accepted Answer · 2018-09-20 18:32:25Z

3

You could use

With Workbooks(bookname).Charts(Chartname)
    .SeriesCollection.NewSeries
    .SeriesCollection(i).XValues = YourXs
    .SeriesCollection(i).values = YourYs
end with

You can choose which series you want to edit by using the index i. This actually sets the (X,Y) pairings while what's below only changes the range shown by the graph.

To change the bounds of your Y axis you can use

.Axes(xlValue).MinimumScale = 
.Axes(xlValue).MaximumScale =

To change the bounds of your x axis use

.Axes(xlCategory).MinimumScale = 
.Axes(xlCategory).MaximumScale =

edited Sep 20, 2018 at 18:32

answered Sep 20, 2018 at 16:24

Jchang43

8916 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Hamouza Over a year ago

Hey ! thanks for your help ! Unfortunately I still cant figure out what Im looking for. Is there no way of doing it by changing the Folrmula of the serie ? Something like: SeriesCollection.Formula = NewFormule ? Thanks in advance!

Jchang43 Over a year ago

Are you trying to alter the data or change the range of the graphs? If you're trying to scale the y values, you might be able to get away with doing some operation on the seriesCollection(i).values and setting it equal back to itself

Hamouza Over a year ago

What Im trying to do is to change the range of the cells in the formula using VBA. for example the serie now is : =SERIES("SO2 U5";'CEMS_U6_YTD 2018 '!$B$2165:$B$2303;'CEMS_U5_YTD 2018'!$D$2165:$D$2312;1) I want to change it using VBA to this : =SERIES("SO2 U5";'CEMS_U6_YTD 2018 '!$C$2165:$C$2303;'CEMS_U5_YTD 2018'!$D$2165:$D$2312;1) It's just an example

Jchang43 Over a year ago

You could accomplish that by using seriescollection(i).Xvalues=activeworkbook.sheets("CEMS_U6_YTD 2018").Range("C2165:C2303").values and seriescollection(i).values=activeworkbook.sheets("CEMS_U6_YTD 2018").Range("D2165:D2312").values

Hamouza Over a year ago

Here's my code : Sub graphique() With ThisWorkbook.Sheets("Weekly Trend") .SeriesCollection(1).XValues = Sheets("CEMS_U5_YTD 2018").Range("C1000:C1500").Values .SeriesCollection(1).Values = Sheets(1).Range("C6:C100").Values End With End Sub It gives me an error 438 I dont know why :(

|

Jon Peltier · Accepted Answer · 2018-09-22 15:37:19Z

You said you wanted to change

=SERIES("SO2 U5",'CEMS_U6_YTD 2018'!$B$2165:$B$2303,'CEMS_U5_YTD 2018'!$D$2165:$D$2312,1)

to this

=SERIES("SO2 U5",'CEMS_U6_YTD 2018'!$C$2165:$C$2303,'CEMS_U5_YTD 2018'!$D$2165:$D$2312,1)

This may be a simple as

ActiveChart.SeriesCollection(1).Formula = _
  "=SERIES(""SO2 U5"",'CEMS_U6_YTD 2018'!$C$2165:$C$2303,'CEMS_U5_YTD 2018'!$D$2165:$D$2312,1)"

note doubling of the double quotes around the series name.

However, since you are only changing the X values, you could use this:

ActiveChart.SeriesCollection(1).XValues = "='CEMS_U6_YTD 2018'!$C$2165:$C$2303"

or

ActiveChart.SeriesCollection(1).XValues = Worksheets("CEMS_U6_YTD 2018").Range("$C$2165:$C$2303")

I've written a tutorial about editing series formulas with VBA: Change Series Formula – Improved Routines.

Collectives™ on Stack Overflow

Change data range in a chart using VBA

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related