Extend plotting range in VBA

[Olibrius]

Hi everybody,
I have a large worksheet with data (always increasing), and I have a lot of graphic sheets each containing a scatter plot of a part of these data. Thus, in these sheets, there is a graphic defined by:
=SERIES(data!$B$1,data!$A$2:$A$1350,data!$B$2:$B$1350,1) for the first sheet,
=SERIES(data!$C$1,data!$A$2:$A$1350,data!$C$2:$C$1350,1) for the second,
=SERIES(data!$D$1,data!$A$2:$A$1350,data!$D$2:$D$1350,1) and so on.
When my data range becomes larger, I have to change $1350 to, say, $1400 in every and each of the charts. And still worse, I have to do it by hand, because search and replace doesn't work on graphics!
That's the reason why I'm looking for a VBA macro I could apply on each graphic sheet to update its range.
If somebody could help me, I'd really appreciate.
Thank you.
O.

 

[JLGWhiz]

You would need to create a chart object varfiable to represent the chart you want to work with. eg. myChart = Sheet1.ChartObjects(1).Chart, or some similar assignment of the object.
Then

Dim myChart As Chart, Ser As Double
Set myChart = Sheets("Sheet1").ChartObjects(1).Chart
Ser = $1400
myChart.SeriesCollection.Extend Worksheets(1).Range(Ser)

The number in parentheses represents the index number of the chart object, so if you have more than one to a sheet, that index number will vary according to which chart you want to work with. If you have three charts, you could

For i = 1 To 3
	Set myChart = Sheets("Sheet1").ChartObjects(i).Chart
	Ser = $1400 'If this value varies per chart then an array would be needed to feed this value as a variable.
	myChart.SeriesCollection.Extend Worksheets(1).Range(Ser)
Next

 

[Olibrius]

You would need to create a chart object variable to represent the chart you want to work with. eg. myChart = Sheet1.ChartObjects(1).Chart, or some similar assignment of the object.
Then [...]
myChart.SeriesCollection.Extend Worksheets(1).Range(Ser)
[...]

Wahoo That's exactly what I needed! I just didn't know how to manipulate the charts in VBA...
Thanks a lot JLGWiz!

 

[JLGWhiz]

The sample I posted is misleading. I copied from some old code of mine and forgot to make the adjustment.
If you are using acual values you would not use the Range(ser) syntax. Use just the ser variable. The Range() is only used when the series is comprised of a range of cell values.

myChart.SeriesCollection.Extend Worksheets(1).Ser

 

[Olibrius]

Thanks for the update! It'll certainly spare me some head scratching...

 

[JLGWhiz]

Thanks for the update! It'll certainly spare me some head scratching...

I didn't do a very good job of updating either.

Set myChart = Sheets("Sheet1").ChartObjects(1).Chart
Ser = $1400
myChart.SeriesCollection.Extend Ser

This is the correct syntax when only adding a value and not a range containing a value.

 

[Olibrius]

Ah, VBA syntax... What an endless source of fun! I believe it's the real root of the trial and error method