Consulting

Results 1 to 5 of 5

Thread: Update Data of Charts

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

    Update Data of Charts

    Hi there,

    I am facing some issue when I want to automate the update of a chart.
    I am updating those charts once a week, and want the last 10 weeks (explaining the offset).
    I have this code, which works, but I would like to know if there is a "simpliest/more elegant" way to proceed.

    wsWar.ChartObjects("Chart 4").ActivateActiveChart.SeriesCollection(1).Values = Range(Cells(35, 6).End(xlToRight).Offset(0, -9), Cells(35, 6).End(xlToRight))        ActiveChart.SeriesCollection(1).XValues = Range(cellweek, cellweek.Offset(0, -9))
            ActiveChart.SeriesCollection(1).Name = "='Warehouse Charts'!$A$5"
            
            ActiveChart.SeriesCollection(2).Values = Range(Cells(36, 6).End(xlToRight).Offset(0, -9), Cells(36, 6).End(xlToRight))
            ActiveChart.SeriesCollection(2).XValues = Range(cellweek, cellweek.Offset(0, -9))
            ActiveChart.SeriesCollection(2).Name = "='Warehouse Charts'!$A$6"
            
            ActiveChart.SeriesCollection(3).Values = Range(Cells(37, 6).End(xlToRight).Offset(0, -9), Cells(37, 6).End(xlToRight))
            ActiveChart.SeriesCollection(3).XValues = Range(cellweek, cellweek.Offset(0, -9))
            ActiveChart.SeriesCollection(3).Name = "='Warehouse Charts'!$A$7"

    Thanks in advance for your help!
    Edmond

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
        Dim cht As Chart
        Dim ser As Series
     
        Set cht = wsWar.ChartObjects("Chart 4").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

  3. #3
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    Than you very much Mana.
    I have a run time error when I run the code:
    Run-time error '1004' : Method 'Range' of object'_Global' failed
    within the line: ser.XValues
    I have already had this issue but I've never really understood what it meant.
    Edmond

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Could you post your workbook?

  5. #5
    VBAX Regular
    Joined
    Oct 2018
    Posts
    43
    Location
    Hey Mana,

    Sorry for the late answer.
    I am not able to provide you my workbook. Let me try to explain you what I have:

    Chart is within a specific worksheet
    Data are with worksheet1

    the Xvalues are based on the week
    Yvalue are based on multiple series.

    You will find below the beginning of my code:

    Sub ChartW()
    
    Dim wsWar As Worksheet
    Set wsWar = Worksheets("W Charts")
    Dim cellweek As Range
    Set cellweek = wsWr.Cells(2, 6).End(xlToRight)
    
    
    'Chart 5 - AOG
        wsWr.ChartObjects("Chart 8").Activate
        
            ActiveChart.SetElement (msoElementChartTitleAboveChart)
            ActiveChart.ChartTitle.Text = "Menu"
            With ActiveChart.ChartTitle.Select
                Selection.Left = 12
                Selection.Top = 6
            End With
        
            ActiveChart.SeriesCollection(3).Values = Range(Cells(7, 6).End(xlToRight).Offset(0, -9), Cells(7, 6).End(xlToRight))
            ActiveChart.SeriesCollection(3).XValues = Range(cellweek, cellweek.Offset(0, -9))
            ActiveChart.SeriesCollection(3).Name = "='W Charts '!$A$7"
            
            ActiveChart.SeriesCollection(2).Values = Range(Cells(6, 6).End(xlToRight).Offset(0, -9), Cells(6, 6).End(xlToRight))
            ActiveChart.SeriesCollection(2).XValues = Range(cellweek, cellweek.Offset(0, -9))
            ActiveChart.SeriesCollection(2).Name = "='W Charts'!$A$6"
    Last edited by Edmond; 01-22-2019 at 09:59 AM.
    Edmond

Tags for this Thread

Posting Permissions

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