Trying to make loop to create series of charts in Excel 2003

[JeffL]

I'm trying to create a loop to generate a new chart for each column in a
spreadsheet. Macro recorder creates code like this, which I want to change
to the indexed cell format:

ActiveChart.SeriesCollection(1).XValues = "=Data!R3C4:R14C4"

I've tried this, but it doesn't seem to work:

ActiveChart.SeriesCollection(1).XValues = Worksheets("Data").Range(Cells(3,
1), Cells(3, 14))

 

[Jon Peltier]

When the chart is active, Excel can't find cells on it. You need something
like this:

ActiveChart.SeriesCollection(1).XValues =
Worksheets("Data").Range(Worksheets("Data").Cells(3, 1),
Worksheets("Data").Cells(3, 14))

or

With Worksheets("Data")
ActiveChart.SeriesCollection(1).XValues = .Range(.Cells(3, 1), .Cells(3,
14))
End With

Note the dots in front of Range and Cells.

- Jon

 

[JeffL]

Thanks, this does exactly what I need.

When the chart is active, Excel can't find cells on it. You need something
like this:

ActiveChart.SeriesCollection(1).XValues =
Worksheets("Data").Range(Worksheets("Data").Cells(3, 1),
Worksheets("Data").Cells(3, 14))

or

With Worksheets("Data")
ActiveChart.SeriesCollection(1).XValues = .Range(.Cells(3, 1), .Cells(3,
14))
End With

Note the dots in front of Range and Cells.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______