That's a different problem. To change the series' name simply write to its
Name property.
If I understand the objective you want to offset all series data by the same
amount. Providing you are sure in advance the same offset will work in all
series (X & Y values & name, possibly bubble sizes) have a go with the
following.
Sub test()
Dim cht As Chart
Set cht = ActiveChart
If cht Is Nothing Then
MsgBox "select a chart"
Exit Sub
End If
OffsetChart ActiveChart, 0, 2
End Sub
Sub OffsetChart(cht As Chart, rowOS As Long, colOS As Long)
Dim i As Long
Dim posL As Long, posR As Long
Dim sFmla As String, sFmlaEnd As String
Dim r As Range
Dim sr As Series
Dim arr
ReDim arrFmlas(1 To cht.SeriesCollection.Count) As String
'store the original formulas for undo just in case
For i = 1 To cht.SeriesCollection.Count
arrFmlas(i) = cht.SeriesCollection(i).Formula
Next
For Each sr In cht.SeriesCollection
sFmla = sr.Formula
posL = InStr(1, sFmla, "(") + 1
posR = InStrRev(sFmla, ")") - 1
arr = Split(Mid$(sFmla, posL, (posR - posL)), ",")
sFmla = Left$(sFmla, posL - 1)
On Error Resume Next
For i = 0 To UBound(arr)
Set r = Nothing
Set r = Range(arr(i))
If Not r Is Nothing Then
arr(i) = r.Offset(rowOS, colOS).Address(external:=True)
End If
sFmla = sFmla & arr(i)
If i < UBound(arr) Then
sFmla = sFmla & ","
Else
sFmla = sFmla & ")"
End If
Next
On Error GoTo errH
sr.Formula = sFmla
Next
Exit Sub
resUndo:
If MsgBox("an error occurred, Undo ?", vbYesNo) = vbYes Then
On Error Resume Next
For i = 1 To UBound(arrFmlas)
cht.SeriesCollection(i).Formula = arrFmlas(i)
Next
End If
Exit Sub
errH:
Resume resUndo
End Sub
Regards,
Peter T
