Source data for chart

[nsv]

I have the following lines of code in a macro:

ActiveSheet.ChartObjects("Prob").Activate
ActiveChart.SeriesCollection(1).XValues = "=prob!R1C1:R45C1"
ActiveChart.SeriesCollection(1).Values = "=prob!R1C2:R45C2"

I want the number of chart data rows (in this example it is 45) to be variable so that this value is taken from a cell in the sheet, but it will not accept a variable name here.

Is this possible?


NSV

[Jon Peltier]

I can't test it on this computer, but try:

RowNum = ActiveSheet.Range("D2").Value
ActiveSheet.ChartObjects("Prob").Activate
ActiveChart.SeriesCollection(1).XValues = "=prob!R1C1:R" & RowNum & "C1"
ActiveChart.SeriesCollection(1).Values = "=prob!R1C2:R" & RowNum & "C2"

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

"nsv" <[email protected]> wrote in message
news:[email protected]...
>
> I have the following lines of code in a macro:
>
> ActiveSheet.ChartObjects("Prob").Activate
> ActiveChart.SeriesCollection(1).XValues = "=prob!R1C1:R45C1"
> ActiveChart.SeriesCollection(1).Values = "=prob!R1C2:R45C2"
>
> I want the number of chart data rows (in this example it is 45) to be
> variable so that this value is taken from a cell in the sheet, but it
> will not accept a variable name here.
>
> Is this possible?
>
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile:
> http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=487226
>

[[email protected]]

I have a similar question. In my case I want to highlight the xy
values for the chart, then chart the data on a scatter plot. The data
are surrounded by other populated columns - so CurrentRegion won't
work. The problem in the skinnied-down macro below is the Range
function takes the hard coded range. How do I pass in the
user-selected range?

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B4:C10")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

[Jon Peltier]

Dim rSelection As Range
If TypeName(Selection) = "Range" Then
Set rSelection = Selection
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=rSelection
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
Else
MsgBox "Select a range and try again", vbCritical
Exit Sub
End If

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


<[email protected]> wrote in message
news:[email protected]...
>I have a similar question. In my case I want to highlight the xy
> values for the chart, then chart the data on a scatter plot. The data
> are surrounded by other populated columns - so CurrentRegion won't
> work. The problem in the skinnied-down macro below is the Range
> function takes the hard coded range. How do I pass in the
> user-selected range?
>
> Charts.Add
> ActiveChart.ChartType = xlXYScatterLines
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B4:C10")
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
>

[nsv]

Thanks Jon, but it doesn't work. The compiler only reads:

ActiveChart.SeriesCollection(1).XValues = "=prob!R1C1:R"

and the rest of the line is turned into rubbish, but I will try what I can do with the other example.

NSV