Automatically create a chart
 

I am in the process of automating a chart creation and get stuck on this line:

'WS is declared as a worksheet
ActiveChart.SeriesCollection(1).values = WS.Range("G34, I34, K34, M34, O34")

I want to create a series in a chart by selecting multiple noncontinguous
cells, but
I run into errors executing this line.

Can someone point out what is wrong?

Thanks,

YH

Automatically create a chart
 

Hi YH

couldn't you fill it with something like this?
ActiveChart.SeriesCollection(1).values =
"=(sheet1!R11C7,Sheet1!R13C7,Sheet1!R18C7,Sheet1!R 20C7)"

hth

Carlo

"YH" wrote:

I am in the process of automating a chart creation and get stuck on this line:

'WS is declared as a worksheet
ActiveChart.SeriesCollection(1).values = WS.Range("G34, I34, K34, M34, O34")

I want to create a series in a chart by selecting multiple noncontinguous
cells, but
I run into errors executing this line.

Can someone point out what is wrong?

Thanks,

YH

Automatically create a chart
 

Unfortunately you cannot apply a non-continguous range like that as your
source, you could convert the values to an array and assign to the
series.Values though the chart won't update if cells change.

It seems you want to plot every other cell in a row. If 'effectively' just
one series and the missing cells are say text maybe this might work for you

ActiveChart.SetSourceData Range("G34:O34"), xlColumns

This should only show the value cells though you might want to delete the
missing series. Alternatively plot each single cell as a series in much the
same way.

Regards,,
Peter T

"YH" wrote in message
...
I am in the process of automating a chart creation and get stuck on this
line:

'WS is declared as a worksheet
ActiveChart.SeriesCollection(1).values = WS.Range("G34, I34, K34, M34,
O34")

I want to create a series in a chart by selecting multiple noncontinguous
cells, but
I run into errors executing this line.

Can someone point out what is wrong?

Thanks,

YH

Automatically create a chart
 

Hi,

It does not like it because it contains multiple areas.
The will construct a reference from the areas. Note there is a limit to
the length of the series formula so lots of area may cause another error
to occur.

'---------------
Dim rngTemp As Range
Dim strAddress As String

For Each rngTemp In Range("C11,C13,C15").Areas
strAddress = strAddress & _
"'" & rngTemp.Parent.Name & "'!" & _
rngTemp.Address(, , xlR1C1) & ","
Next
strAddress = "=(" & Left(strAddress, Len(strAddress) - 1) & ")"

ActiveChart.SeriesCollection(1).Values = strAddress
'---------------

Cheers
Andy

YH wrote:
I am in the process of automating a chart creation and get stuck on this line:

'WS is declared as a worksheet
ActiveChart.SeriesCollection(1).values = WS.Range("G34, I34, K34, M34, O34")

I want to create a series in a chart by selecting multiple noncontinguous
cells, but
I run into errors executing this line.

Can someone point out what is wrong?

Thanks,

YH

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Automatically create a chart
 

Since I will be creating 3 to 4 charts each on different worksheets, is there
a way I can substitue the activechart name and sheet name with variables?
That way I can use the same set of scripts to generate charts for different
worksheets?

Can someone show me how to change this line below to use variable names for
the activechart and sheet?

ActiveChart.SeriesCollection(1).values =
"=(sheet1!R11C7,Sheet1!R13C7,Sheet1!R18C7,Sheet1!R 20C7)"

Thanks,

YH

Automatically create a chart
 

The code I posted deals with the sheet naming once you specify the range
to use.
You can use the chartobjects collection to loop through the charts on a
sheet, but only you know which range to use with what chart.

Cheers
Andy

YH wrote:
Since I will be creating 3 to 4 charts each on different worksheets, is there
a way I can substitue the activechart name and sheet name with variables?
That way I can use the same set of scripts to generate charts for different
worksheets?

Can someone show me how to change this line below to use variable names for
the activechart and sheet?

ActiveChart.SeriesCollection(1).values =
"=(sheet1!R11C7,Sheet1!R13C7,Sheet1!R18C7,Sheet1!R 20C7)"

Thanks,

YH

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info