charting series

[pj]

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

 

[Guest]

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]

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