Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create line chart with two axises in Pivot Chart | Charts and Charting in Excel | |||
How to automatically create a thumbnail of a published chart/image | Charts and Charting in Excel | |||
Chart data file lost - need to re-create from chart? | Charts and Charting in Excel | |||
able to automatically create a chart in Excel | Charts and Charting in Excel | |||
Macro to Create a Chart and Update it Automatically | Charts and Charting in Excel |