CODEWITHSUNDEEP
excelvbacharts
Hamouza
Hamouza

Reputation: 356

Change data range in a chart using VBA

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:

enter image description here

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 !

Upvotes: 2

Views: 13402

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

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.

Upvotes: 1

Jchang43
Jchang43

Reputation: 891

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 =

Upvotes: 3

Related Questions