Changing Excel chart seris via VBA - Rookie needs help

[GiraffetheGeek]

Hi all,

I have a chart that it based on only 4 lines of data at present. However as time goes on the line of data will increase.

I have written some code to update the charts data source as below:

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
        
End Sub

However when this code is run I get the following 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 - guess not

 

[Ralajer]

The ActiveCell.Offset(0, 1).Select is not offsetting rows but columns, Offset's arguments are ordered thusly Offset(Rows, Columns). As written you will always have the end row be 1. While that is a problem it's not the cause of the 1004 error.

I can't reproduce the error in Excel 2010. What version of Excel are you running? What type of chart is this and which line is highlighted when you get the error message?

This is just quick rewrite to address the end row issue and while I changed some the other code as well it probably won't fix the run-time error.

Sub FormatChart()
    
    '// Finds the last row of continuous Data from A1 down
    '// Problem if only one row of data. Other ways of doing this but that depends on
    '// how the data is setup
    endRow = Sheets("ChartData").Range("A1").End(xlDown).Row
    
    Sheets("Running Results").ChartObjects("Chart 1").Activate
    
    With ActiveChart
        .ChartArea.Select
        .SeriesCollection(1).Values = "='ChartData'!$B$1:$B$" & endRow
        .SeriesCollection(2).Values = "='ChartData'!$E$1:$E$" & endRow
        .SeriesCollection(3).Values = "='ChartData'!$H$1:$H$" & endRow
        .SeriesCollection(4).Values = "='ChartData'!$K$1:$K$" & endRow
    End With
End Sub

 

[GiraffetheGeek]

Hi Ralajer,

Thanks for the input. It is a line graph I am using and it is Excel 2003 with Visual Basic 6.5.

I fixed the enrow issue and tried your rewrite with the chart and I get the same error.

The line highlighted is

.SeriesCollection(1).Values = "='ChartData'!$B$1:$B$" & endrow

I have gone through and double checked all the names for spelling etc

 

[GiraffetheGeek]

I have now found a solution.

Thanks to another MrExcel contributor Andrew Poulsom and another thread I found during a routine Google search.

The solution looks like this:

Sub FormatChart()
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Rng4 As Range
    
    Sheets("ChartData").Activate
    Range("A1").Select
    If ActiveCell <> "" Then
        Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
        Loop
    End If
    ActiveCell.Offset(-1, 0).Activate
    endrow = ActiveCell.Row
    'MsgBox ("End row = " & endrow)
    
    Sheets("Running Results").Activate
    
    Set Rng1 = Worksheets("ChartData").Range("B1:B" & endrow)
    Set Rng2 = Worksheets("ChartData").Range("E1:E" & endrow)
    Set Rng3 = Worksheets("ChartData").Range("H1:H" & endrow)
    Set Rng4 = Worksheets("ChartData").Range("K1:K" & endrow)
        
    ActiveSheet.ChartObjects("Chart 11").Activate
    With ActiveChart
        .ChartArea.Select
        .SeriesCollection(1).Values = Rng1
        .SeriesCollection(2).Values = Rng2
        .SeriesCollection(3).Values = Rng3
        .SeriesCollection(4).Values = Rng4
    End With
End Sub

Now I just need to teach myself how to change the X axis catagory but that should be elementary now that I have that figured out.

 

[Jon Peltier]

1. If you apply the address of a range, until 2010 it needed to be given in R1C1 notation.

2. To get the category labels, they all use the same, so define another range (Rng0) and set the XValues of a series:

.SeriesCollection(1).XValues = Rng0