Changing formulae for series in line charts

[=?iso-8859-1?Q?Ulrike_Gr=F6mping?=]

Dear all,

I'm trying to change the formulae in many line charts by
adding four points
to each chart, i.e. the old range goes through to
column "Z", the new range
is supposed to go through to column "AD".

Whenever I do this, the third series gets the data from
the first series
(subsequent series are ok again), and I have no clue, why
that might be the
case. Any ideas?

Regards, Ulrike

Code:
Sub makelonger()
For i = 12 To 19
For j = 1 To Worksheets(i).ChartObjects.Count
anz = Worksheets(i).ChartObjects
(j).Chart.SeriesCollection.Count
For k = 1 To anz
titel = Worksheets(i).ChartObjects
(j).Chart.Name
On Error GoTo naechste
formel =
Worksheets(i).ChartObjects(j).Chart.SeriesCollection
(k).Formula
formelneu = formel
If InStr(formel, "Z") > 0 Then _
formelneu = Left(formel, InStr
(formel, ":$Z")) & "$AD" & _
Right(formel, Len(formel) - InStr
(formel, "Z"))
If InStr(formelneu, "Z") > 0 Then _
formelneu = Left(formelneu, InStr
(formelneu, ":$Z")) & "$AD"
& _
Right(formelneu, Len(formelneu) - InStr
(formelneu, "Z"))
Worksheets(i).ChartObjects
(j).Chart.SeriesCollection(k).Formula
= formelneu
naechste:
Next k
Next j
Next i
End Sub

 

[=?iso-8859-1?Q?Ulrike_Gr=F6mping?=]

It is not the third series but the last series of each
chart that gets the same data as the first series. But I
still don't have a clue why.

Regards, Ulrike

 

[Bill Renaud]

Try this revised (but untested) code. I converted most of your code over to
object variables, to make debugging easier. You should be able to
single-step through the code and use the Locals window to verify what the
objects are actually set to. This will also allow you to see what properties
and methods would be useful at each stage of the process. The Replace
function should replace each occurence of "$Z" in your series formulas and
should be a much easier way to do it. Your code line-wrapped pretty bad in
your post, but I think I got it okay.

Sub makelonger()
Dim lngWorksheetNumber As Long
Dim ws As Worksheet
Dim objChart As Chart
Dim objSeries As Series

For lngWorksheetNumber = 12 To 19
Set ws = Worksheets(lngWorksheetNumber)

For Each objChart In ws.ChartObjects
For Each objSeries In objChart.SeriesCollection
'Don't know what this unneeded code is; must be global variable.
titel = objChart.ChartTitle

On Error GoTo naechste

With objSeries
.Formula = Replace(Expression:=.Formula, _
Find:="$Z", _
Replace:="$AD", _
Compare:=vbTextCompare)
End With
naechste:
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub

 

[=?iso-8859-1?Q?Ulrike_Gr=F6mping?=]

Bill,

thanks for your suggestion, and sorry for the chaotic
layout of my code. I've tried your code (thanks for the
replace, that's much better than my version), and I
modified it slightly because I couldn't make the Chart
Objects work otherwise (see below). However, it comes up
with the same faulty result as my code does.

Actually, by single-stepping my original code, I found
that the error happens quite early: when the program is
supposed to grab the formula of the last series of a
chart, it does in fact grab the formula of the first
series. And oddly enough, when writing to the formula of
the last series, it d o e s grab the last series, so in
fact in overwrites the last series with the first series.
And the same happens in your code as well (though not
separated into two steps). I have no idea whether that is
a special issue of my workbook with its special charts
(which I inherited from somebody) or whether that is a bug
or ...

Any further ideas are welcome, although I have by now
solved my application problem by running the code on all
but the last series and modifying the last series manually.

Regards, Ulrike

Code:
Sub makelonger()
Dim lngWorksheetNumber As Long
Dim ws As Worksheet
Dim objChart As Chart
Dim objSeries As Series

For lngWorksheetNumber = 12 To 19
Set ws = Worksheets(lngWorksheetNumber)
anz = ws.ChartObjects.Count

For i = 1 To anz
Set objChart = ws.ChartObjects(i).Chart
For Each objSeries In objChart.SeriesCollection
'Don't know what this unneeded code is; must be
global variable.

On Error GoTo naechste

With objSeries
.Formula = Replace(Expression:=.Formula, _
Find:="$Z", _
Replace:="$AD", _
Compare:=vbTextCompare)
End With
naechste:
Next objSeries
Next i
Next lngWorksheetNumber
End Sub

 

[Bill Renaud]

OK, I did some more work with the debugger and revised the code again. Since
I did not have your workbook to use to debug with, I had to set up something
of my own to test with.

Since your charts are embedded on a worksheet, objChart must be declared as
a ChartObject, instead of a Chart (which is for a stand-alone chart sheet).
Also, notice the addition of ".Chart." in the middle of the 2nd For Each
statement. I also changed the Error handler. Leaving it embedded in the
middle of the routine and simply trying to jump to another point in the
program does not reset any error when it occurs. You have to either Resume
Next, or actually go to an error handler at the bottom of the routine and
then somehow clear the error before continuing (use Err.Clear or Resume Next
in the error handler).

Does your last series use named ranges or some sort of dynamic charting or
indirect referencing technique, that would cause the problem? I am also not
sure about how robust the Replace technique is for extending a data range.
What if the worksheet is currently being displayed in R1C1 reference style
(Tools|Options General tab)?
'----------------------------------------------------------------------
Sub makelonger()
Dim lngWorksheetNumber As Long
Dim ws As Worksheet
Dim objChart As ChartObject
Dim objSeries As Series

On Error Resume Next

For lngWorksheetNumber = 12 To 19
Set ws = Worksheets(lngWorksheetNumber)

For Each objChart In ws.ChartObjects
For Each objSeries In objChart.Chart.SeriesCollection
With objSeries
.Formula = Replace(Expression:=.Formula, _
Find:="$Z", _
Replace:="$AD", _
Compare:=vbTextCompare)
End With
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub

 

[=?iso-8859-1?Q?Ulrike_Gr=F6mping?=]

Hi Bill,

thanks for the additional work. Your code works now fine
for me, but the last series is still treated like the
first one.

I've also tried your code on other charts, and there it
works (like mine does as well). This means that there is
something very odd going on on my charts. I cannot detect
any difference between the last and the other series, they
all have a specific range for name, values, and x-axis
labels (where x-axis labels are the same for all three (or
six) series. Originally, the range for name was sometimes
the same for several series, and it might even be the case
that it was primarily so for fist and last series.
However, would that mean that Excel is forever unable to
correctly identify the series number?
I'm quite confused about this issue...

Regards, Ulrike

 

[Bill Renaud]

Hi Ulrike,

Could you reply with what the series formula for that last series is before
running the macro, what it is supposed to be after running the macro, and
then what it actually is after the macro?

i.e. =SERIES(,Data!$A$2:$A$8,Data!$B$2:$B$8,1)

How many series formulas are there in your chart? Is the last series part of
an overlay chart? Maybe I can see if I can duplicate this behaviour on my
machine (Excel 2000 SP-3).