.XValues syntax problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am writing a macro that includes assigning x-values from the third column
of Sheet 1 to the active chart:

ActiveChart.SeriesCollection(1).XValues = Sheets("Sheet1").Range("C2:C105")

However, when I change the range-referencing to cells-style, I get an error:

ActiveChart.SeriesCollection(1).XValues =
Sheets("Sheet1").Range(Cells(1,3),Cells(105,3))

The error says: "Method of 'Cells' of object '_Global' failed

Does the XValues argument not take Cells-style referencing? I ask because
what I really want to do is assign a variable to the range that changes
according to the number of rows in the column that contain data:

ActiveChart.SeriesCollection(1).XValues =
Sheets("Sheet1").Range(Cells(1,3),Cells(num_rows,3))
 
You need to reference the cells:

ActiveChart.SeriesCollection(1).XValues = _
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1,3),
Sheets("Sheet1").Cells(105,3))

or

With Sheets("Sheet1").
ActiveChart.SeriesCollection(1).XValues = .Range(.Cells(1,3),
..Cells(105,3))
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top