Error '1004' when adding individual cells to a chart range in a loop

Question

I'm trying to add individual cells to a chart so that only cells that meet a certain criteria are added to the chart.

ChartXDataString = "="
ChartFuelDriveDataString = "="

For i = 0 To 5 

    If Range("AP" & FirstDataRow + i) <> "0" Then

        If i = 5 Then
            ChartXDataString = ChartXDataString & "'RAW FleetData'!$A$" & (FirstDataRow + i)
            ChartFuelDriveDataString = ChartFuelDriveDataString & "'RAW FleetData'!$AP$" & (FirstDataRow + i)
        Else
            ChartXDataString = ChartXDataString & "'RAW FleetData'!$A$" & (FirstDataRow + i) & ";"
            ChartFuelDriveDataString = ChartFuelDriveDataString & "'RAW FleetData'!$AP$" & (FirstDataRow + i) & ";"
        End If
    End If
Next i

ActiveSheet.ChartObjects("gFMP_TotalFuelLiters").Activate
ActiveChart.SeriesCollection(1).XValues = ChartXDataString
ActiveChart.SeriesCollection(1).Values = ChartFuelDriveDataString

When I run the code Excel throws the following error: Run-time error '1004': Application-defined or object-defined error.

It does so on the line: ActiveChart.SeriesCollection(1).XValues = ChartXDataString

Even when I record a macro adding individual cells, the chart works until I run the recorded macro, where it throws the error.

Help would be much appreciated!

What is the value of ChartXDataString when the error raises? — David Zemens
I think the problem is that you can't assign formula to .values and .xvalues properties of SeriesCollection object. Try to use .Formula property but change the way you create your chartXDataString or CharterFuelDriveDataString variables. — Kazimierz Jawor

Thomas Olergård Thomas Olergård · Accepted Answer · 2013-07-09T06:50:26

Turns out the macro recorder recorded in the wrong format. Instead of, as the recorder made, ChartXDataString = "='RAW FleetData'!$A$2;'RAW FleetData'!$A$3;'RAW FleetData'!$A$4", the correct format is "=('RAW FleetData'!$A$2,'RAW FleetData'!$A$3,'RAW FleetData'!$A$4)" with parentheses and comma separator

Error '1004' when adding individual cells to a chart range in a loop

1 Answers