Editing Charts in VBA

[GiraffetheGeek]

Hi there,

I have a spreadsheet in which I have a graph.

At the moment the chart is only made up of for lines of data, but as time goes on this will increase.

I want the chart to update as time goes on so I have devised this peice of code to update the source data for the chart.

Sub FormatChart()
    
    Sheets("ChartData").Select
    Range("A1").Select
    If ActiveCell <> "" Then
        Do Until ActiveCell.Value = ""
        ActiveCell.Offset(0, 1).Select
        Loop
    End If
    ActiveCell.Offset(0, -1).Select
    endrow = ActiveCell.Row
    
    Sheets("Running Results").Select
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Values = "='ChartData'!$B$1:$B$" & endrow
    ActiveChart.SeriesCollection(2).Values = "='ChartData'!$E$1:$E$" & endrow
    ActiveChart.SeriesCollection(3).Values = "='ChartData'!$H$1:$H$" & endrow
    ActiveChart.SeriesCollection(4).Values = "='ChartData'!$K$1:$K$" & endrow
    ActiveChart.SeriesCollection(5).Values = "='ChartData'!$N$1:$N$" & endrow
    
End Sub

I get the error "Run-time error '1004' Unable to set the Values property of the Series class.

I sorta got out of my depth with this, but thought I might be able to teach myself something. Looks like not

Can anyone help?

 

[xenou]

Duplicate:
http://www.mrexcel.com/forum/showthread.php?t=575290

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

xenou

 

[GiraffetheGeek]

Oops Sorry,

I thought I had posted already, but couldn't find my original post so I thought I must have not posted it.

My bad