Consulting

Results 1 to 10 of 10

Thread: Chartobjectsd and SeriesCollection

  1. #1
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location

    Chartobjectsd and SeriesCollection

    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
    Last edited by Edmond; 01-28-2019 at 03:13 PM.
    Edmond

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    679
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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!
    Edmond

  4. #4
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    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.
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  5. #5
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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...
    Edmond

  6. #6
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    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

  7. #7
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    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!
    Edmond

  8. #8
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    [a1:a5].resize(10)

  9. #9
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    Great! My gratitude for your help!

    Have a nice weekend!
    Edmond

  10. #10
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    469
    Location
    That's alright! have a nice weekend!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •