charting series
 

Hi,
I am trying to code a chart series using a program
variable. Selecting the range by hand works ok but that's
a hard-select range. Ie, this works:

Sheets("Chart1").Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "=Query1!
R2C7:R19C7"
ActiveChart.SeriesCollection(3).Name = "=""25% Value"""
ActiveChart.SeriesCollection(3).Select
...

As you can see the .Values are hardcoded to R2C7 to R19C7.
I would like to use a variable range that is determined by
the program. I have tried this:

....Values = .Range(.Cells(2, 7), .Cells(c, 7))

where c is computed, but I get a compile error: Invalid or
unqualified reference with the .Cells being highlighted. I
have already dimmed cell as range.

What am I missing here? And is this idea even do-able? TIA
pj

charting series
 

Try the following code
Dim newRange As Range
Dim nSeries As Integer

Set newRange = ActiveWorkbook.Worksheets("Query1") _
.Range("G2").Resize(c -1 , 1)

Sheets("Chart1").Select
ActiveChart.SeriesCollection.NewSeries
nSeries = ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(nSeries).Values = newRange




"pj" wrote:

Hi,
I am trying to code a chart series using a program
variable. Selecting the range by hand works ok but that's
a hard-select range. Ie, this works:

Sheets("Chart1").Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "=Query1!
R2C7:R19C7"
ActiveChart.SeriesCollection(3).Name = "=""25% Value"""
ActiveChart.SeriesCollection(3).Select
...

As you can see the .Values are hardcoded to R2C7 to R19C7.
I would like to use a variable range that is determined by
the program. I have tried this:

....Values = .Range(.Cells(2, 7), .Cells(c, 7))

where c is computed, but I get a compile error: Invalid or
unqualified reference with the .Cells being highlighted. I
have already dimmed cell as range.

What am I missing here? And is this idea even do-able? TIA
pj

charting series
 

That appears to work. I'm not sure why when re-sizing it's
c-1 raather than c but it works. Thanks.
pj

-----Original Message-----
Try the following code
Dim newRange As Range
Dim nSeries As Integer

Set newRange = ActiveWorkbook.Worksheets("Query1") _
.Range("G2").Resize(c -1 , 1)

Sheets("Chart1").Select
ActiveChart.SeriesCollection.NewSeries
nSeries = ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(nSeries).Values = newRange




"pj" wrote:

Hi,
I am trying to code a chart series using a program
variable. Selecting the range by hand works ok but
that's
a hard-select range. Ie, this works:

Sheets("Chart1").Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "=Query1!
R2C7:R19C7"
ActiveChart.SeriesCollection(3).Name = "=""25%
Value"""
ActiveChart.SeriesCollection(3).Select
...

As you can see the .Values are hardcoded to R2C7 to
R19C7.
I would like to use a variable range that is determined
by
the program. I have tried this:

....Values = .Range(.Cells(2, 7), .Cells(c, 7))

where c is computed, but I get a compile error: Invalid
or
unqualified reference with the .Cells being
highlighted. I
have already dimmed cell as range.

What am I missing here? And is this idea even do-able?
TIA
pj

.