Chartobjectsd and SeriesCollection

[Edmond]

Hi Everyone,

The question might be seemed silly bit I couldn't find a way to figure something.

I am trying to update the SeriesCollection of a chart within a Chart Sheet.

Usually, if the chart is a regular worksheet, I use the following code:

Sub Test()

Dim Sh As Worksheet
Dim Cht As ChartObjects
Dim Ser As Series


Set Sh = Worksheets("MC-40 Graph")
Set Cht = Sh.ChartObjects("MC-40 Graph").Chart




    For Each Ser In Cht.SeriesCollection
        Ser.XValues = Range(Split(Ser.Formula, ",")(1)).Offset(, 1)
        Ser.Values = Range(Split(Ser.Formula, ",")(2)).Offset(, 1)
    Next


End Sub

Unfortunately it doesn't work my chart sheet.
Could you help me on this issue?


Thanks a lot

[Dave]

For Each Ser In Cht.SeriesCollection
        Ser.XValues = SH.Range(Split(Ser.Formula, ",")(1)).Offset(, 1)
        Ser.Values = SH.Range(Split(Ser.Formula, ",")(2)).Offset(, 1)
    Next

I think range refers to the chart sheet.... may need SH.Ser.Formula as well? HTH. Dave

[Edmond]

Hi Dave,

Thank you for your help.
I apologize in advance if my questions are silly ones.

The first issue I am facing is that I cannot set "MC-40 Graph" (my chart sheet) as a worksheet. It obviously looks logical but I don't know how to proceed then.
If I am not mistaken if I choose to use "ChartObjects" it has to be preceded by a sheet variable.

So I guess the first thing I have to fix is that line:

Set Cht = Sh.ChartObjects("MC-40 Graph").Chart

Would you have some ideas?


Thanks again!

[JonPeltier]

Set cht = Charts("Name of the Chart Sheet")

or perhaps, if you like being precise,

Set cht = ActiveWorkbook.Charts("Name of the Chart Sheet")

But then, I think it should work the same.

[Edmond]

Hi Jon,

Thank you for your help.

Let me explain it again, I think my explanations were not as accurate.

I have a regular worksheet, I created a Table within it. I update the data with the following code:

Sub Test()


Dim Sh As Worksheet
Dim Cht As Chart
Dim Ser As Series


Set Sh=Worksheets("Calculations 2")
Set cht = Sh.ChartObjects("Chart 1").Chart




    For Each ser In cht.SeriesCollection
        ser.XValues = Range(Split(ser.Formula, ",")(1)).Offset(, 1)
        ser.Values = Range(Split(ser.Formula, ",")(2)).Offset(, 1)
    Next
End Sub

It works perfectly. However when I move the chart to a different sheet (Right click - move chart - new sheet), I cannot find a way to make the code above works. I always end up with a "Subscript out of range". This is my issue...

[大灰狼1976]

Hi Edmond! Modified but not tested.

Sub Test()
Dim Sh As Chart
Dim Ser As Series
Set Sh = Sheets("MC-40 Graph")
    For Each Ser In Sh.SeriesCollection
        Ser.XValues = Range(Split(Ser.Formula, ",")(1)).Offset(, 1)
        Ser.Values = Range(Split(Ser.Formula, ",")(2)).Offset(, 1)
    Next
End Sub

[Edmond]

Great! Thank you so much it works perfectly!

I would have another question.

The Macro allows me to "move" the selection thanks to the Offset.

But I want to "extend" it instead of moving it. E.g.:

Go from A1:A5 to A1:A10 instead of A1:A5 to A5:A10


Thanks again!

[大灰狼1976]

[a1:a5].resize(10)

[Edmond]

Great! My gratitude for your help!

Have a nice weekend!

[大灰狼1976]

That's alright! have a nice weekend!