Setting Range in Graph

[Guest]

I have a worksheet called graphdata and another called charts. on the charts
worksheet there is chart 4. I am trying to dynamically set the range for the
chart as follows

Sheets("Charts").Activate
Range("A1").Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=GraphData!$B$4:$B" & lastrow
ActiveChart.SeriesCollection(2).Values = "=GraphData!$C$4:$C" & lastrow
ActiveChart.SeriesCollection(3).Values = "=GraphData!$I$4:$i" & lastrow

lastrow is determined by using another and in this particular case is 34.

the actual chart series reads

=GraphData!$B$4:$B$32 and its that last number I want to dynamically change
depending on the number of rows in the data

Any suggestions

 

[Jim Cone]

Any suggestions,

The Series Values accepts a Range object or an array of values.
You have neither. Something like the following should work, assuming that
"lastrow" actually contains a numeric value...

With Worksheets("GraphData")
ActiveChart.SeriesCollection(1).Values = .Range(.Cells(4, 2), .Cells(lastrow, 2))
ActiveChart.SeriesCollection(2).Values = .Range(.Cells(4, 3), .Cells(lastrow, 3))
ActiveChart.SeriesCollection(3).Values = .Range(.Cells(4, 9), .Cells(lastrow, 9))
End With

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Nigel" <[email protected]>
wrote in message
I have a worksheet called graphdata and another called charts. on the charts
worksheet there is chart 4. I am trying to dynamically set the range for the
chart as follows

Sheets("Charts").Activate
Range("A1").Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=GraphData!$B$4:$B" & lastrow
ActiveChart.SeriesCollection(2).Values = "=GraphData!$C$4:$C" & lastrow
ActiveChart.SeriesCollection(3).Values = "=GraphData!$I$4:$i" & lastrow

lastrow is determined by using another and in this particular case is 34.

the actual chart series reads

=GraphData!$B$4:$B$32 and its that last number I want to dynamically change
depending on the number of rows in the data

Any suggestions

 

[Guest]

I would most likely use a Dynamic Named range instead. See Debra Dalgleish's
site:
http://www.contextures.com/xlNames01.html

For a VBA solution:

Dim cht As Chart
Dim lastrow As Long

lastrow = Range("B4").End(xlDown).Row
Set cht = ActiveSheet.ChartObjects("Chart 4").Chart
With cht
.SeriesCollection(1).Values = Range("B4:B" & lastrow)
.SeriesCollection(2).Values = Range("C4:C" & lastrow)
.SeriesCollection(3).Values = Range("I4:I" & lastrow)
End With

Regards,
Greg