Excel VBA - Get chart data range

Question

I want to add data to a bunch of existing charts. Assume that each chart has a different number of data series and that the location of the raw data is somewhere in the same workbook. Here's what I'm starting with:

For iChart = 1 To iCount
    ActiveSheet.ChartObjects("Chart " & iChart).Activate
    intSeries = 1
    Do Until ActiveChart.SeriesCollection(intSeries).Name = ""
        Set rXVal = ActiveChart.SeriesCollection(intSeries).XValues '<- Object Required error
        Set rXVal = Range(rXVal, rXVal.End(xlDown))
        Set rYVal = ActiveChart.SeriesCollection(intSeries).Values
        Set rYVal = Range(rYVal, rYVal.End(xlDown))
        ActiveChart.SeriesCollection(intSeries).XValues = rXVal
        ActiveChart.SeriesCollection(intSeries).Values = rYVal
        intSeries = intSeries + 1
    Loop
Next iChart

I know that ActiveChart...XValues = rXVal works, but I'm getting an "Object Required" error on the Set rXVal = ActiveChart....XValues line. I'm assuming that since a range went in to define the data series, I can get that range back out again and then add to it.

UPDATE
To clarify things a little, I have accelerometers in 8 places and FFT software setup to record peak vibration response in 4 separate frequency bands. This yields 32 data points per sample. When exporting, the software spits out an Excel workbook with 4 sheets; one for each frequency band. Each sheet has the accelerometer names going across and sample numbers going down.

Answer 1

I have succeeded using this syntax:

Dim rXVal() As Variant
rXVal = ActiveChart.SeriesCollection(intSeries).XValues

UPDATE

In this case you get an array, because your given statement (ActiveChart.SeriesCollection(intSeries).XValues) is an array and not a range. This is what you see in Locals window if you dig into Series object of ActiveChart.SeriesCollection(intSeries):

(in my dummy data I have rows named r1, r2, r3, r4.)

What I want to say, XValues does not have any property which would indicate its occupied range.

If you actually need a range, I would suggest getting it from the formula property. And the way I would suggest is replacing your error causing line with this one:

Set rXVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(1))

Next, I see you trying to get the range for Values. Similarly, use this:

Set rYVal = Range(Split(ActiveChart.SeriesCollection(intSeries).Formula, ",")(2))

Another thing.

The following lines will cause you an error finally:

intSeries = 1
Do Until ActiveChart.SeriesCollection(intSeries).Name = ""
    ...some code...
    intSeries = intSeries + 1
Loop

Do change them with:

For intSeries = 1 To ActiveChart.SeriesCollection.Count
    ...some code...
Next

Yet another thing.

Consider using With and End With, as you repeat a lot ActiveChart.SeriesCollection(intSeries). Then your code will be much more readable, as you would just skip this long line! Wouldn't that be awesome???