Please help with Range Cells

[vickie_raven]

This is what the macro produced:

ActiveChart.SeriesCollection(1).Values = "='Daily Input'!R6C9:R17C9"



This is what I tried to do - but it gives an error.
I need to substitute the values in the Cells reference with vairables
for the module work correctly.

ActiveChart.SeriesCollection(1).Values = Worksheets("Daily
Input").Range(Cells(6, 9), Cells(17, 9))


Please tell me what I am doing wrong.

 

[Jim Cone]

Vickie,

Hello Again,

It appears that your chart is on a sheet separate from that of
the chart data.
If that is the case then you need to qualify the Cells reference.
"Cells" without a qualifier refers to the active sheet.
So the reference should look like this...

Worksheets("Daily Input").Range(Worksheets("Daily Input").Cells(6, 9), _
Worksheets("Daily Input").Cells(17, 9))

Which can be written a little more efficiently as...
With Worksheets("Daily Input")
.Range(.Cells(6, 9), .Cells(17, 9))
End With
(notice the "." before Range and Cells.

Regards,
Jim Cone
San Francisco, USA

 

[Greg Wilson]

Vickie,

The following code works for me. Ensure that the chart is NOT activated.
This assumes that the chart involved is the first one added to the worksheet.
Change the index number in the line "ChartObjects(1)" to suit if not.

Sub ChangeSeriesVals()
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Daily Input")
Set rng = ws.Range(Cells(6, 9), Cells(10, 9))
With ws.ChartObjects(1).Chart.SeriesCollection(1)
.Values = rng
End With
End Sub

My read is that when the chart is active, it will only accept series
manipulation in terms of a text string in R1C1 notation. Just ensure that it
is NOT active. The expression:

..Values = "='Daily Input'!$I$6:$I$17" failed even though the chart series
formula lists it as such.

The expression:
..Values = "='Daily Input'!R6C9:R17C9" was accepted even when A1 style
notation is selected (Options menu). Presumably R1C1 is native to Excel or at
least to the charting aspect.

Your expression works given the chart is not active and you refer to the
chart as follows:
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) _
..Values = Worksheets("Daily Input"). _
Range(Cells(6, 9), Cells(17, 9))

Be advised I'm just an amateur trying to upgrade his computer skills. Hope
this was helpful.

Regards,
Greg

 

[Greg Wilson]

Further to my post, if Sheets("Daily Input") is NOT the active sheet, then
Jim's comment applies. Assuming that the chart is not active, this should
work:

With Sheets("Daily Input")
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) _
.Values = .Range(.Cells(6, 9), .Cells(17, 9))
End With