.SetSourceData Invalid Parameter error

[cwatumull]

Hi, I'm working on code that loops through different user IDs and for each user ID, the code updates a set of charts based on that user's unique data.

The code below activates a Chart object (hpu), sets the source data to a Range object (hpu_range), plots the data by rows instead of columns, then tries to manipulate the Series object named '"SEC Average".

With some user IDs, the code works great, and then with other user IDs the code doesn't work at all, throwing a Run-time error '1004': Invalid Parameter error message on the following line, as if the code does not recognize the Series object "SEC Average":

 With .SeriesCollection("SEC Average")

When I exit VBA and manually click 'Select Data' in the Chart Tools, I see that the correct range is in fact input into the Data Range entry and there is, in fact, a 'SEC average' Series. When I click 'Okay' in the 'Select Data' dialogue box (without doing anything else), the chart then populates with the correct data.

For some reason, it seems that as I am iterating through this code, the SetSourceData method isn't working properly... Anyone know what's going on?

 With hpu

    .Activate
    .SetSourceData Source:=hpu_range
    .PlotBy = xlRows
    With .SeriesCollection("SEC Average")
        .AxisGroup = xlSecondary
        .Border.Color = RGB(204, 102, 102)
    End With

 

[John_w]

I can't explain the error, but try referencing the series by index instead, assuming "SEC Average" is the first data series:

    With .SeriesCollection(1)
        .AxisGroup = xlSecondary
        .Border.Color = RGB(204, 102, 102)
    End With