Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with Range Cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with Range Cells
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 "vickie_raven" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with Range Cells
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 "vickie_raven" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with Range Cells
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 "Greg Wilson" wrote: 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 "vickie_raven" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a range of cells based on criteria in another range | Excel Worksheet Functions | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement | Excel Programming |