Thread: .XValues syntax problem

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/
_______


"JF_01" <JF_01@discussions.microsoft.com> wrote in message
news:BF38348D-5BF0-465A-B52E-3FA0F9717B7C@microsoft.com...
>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))