Changing formulae for series in line charts
 

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.SeriesCollecti on
(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

Correction of question: Changing formulae for series in line charts
 

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

-----Original Message-----
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.SeriesCollect ion
(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



.

Correction of question: Changing formulae for series in line charts
 

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", _
Compa=vbTextCompare)
End With
naechste:
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub
--
Regards,
Bill


"Ulrike Grömping" wrote in message
...
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

-----Original Message-----
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.SeriesCollect ion
(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



.

Correction of question: Changing formulae for series in line charts
 

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", _
Compa=vbTextCompare)
End With
naechste:
Next objSeries
Next i
Next lngWorksheetNumber
End Sub




-----Original Message-----
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", _
Compa=vbTextCompare)
End With
naechste:
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub
--
Regards,
Bill


"Ulrike Grömping"
wrote in message
...
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

-----Original Message-----
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.SeriesCollect ion
(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



.



.

Correction of question: Changing formulae for series in line charts
 

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", _
Compa=vbTextCompare)
End With
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub
--
Regards,
Bill

Correction of question: Changing formulae for series in line charts
 

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

-----Original Message-----
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", _
Compa=vbTextCompare)
End With
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub
--
Regards,
Bill


.

Correction of question: Changing formulae for series in line charts
 

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).
--
Regards,
Bill


"Ulrike Grömping" wrote in message
...
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

-----Original Message-----
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", _
Compa=vbTextCompare)
End With
Next objSeries
Next objChart
Next lngWorksheetNumber
End Sub
--
Regards,
Bill


.