|
Chart update using VBA
Assuming xT refers to a suitable range in a single row or column of cells,
and the chart contains two series, maybe the data when converted to an array like this "{1 ,2, 3}" is more then 255 characters incl. brackets, commas & spaces Normally it's more convenient to link to data in cells, you could re-write the series formula or try this s = "=" & xT.Address(, , xlR1C1, True) ActiveChart.SeriesCollection(2).Values = s Regards, Peter T "Nigel" wrote in message ... I have some code that set the range for two series charts on one worksheet. The charts are embedded on a worksheet, and the ranges xV, xX and xT are set within the code and are valid references pointing to the data required. My problem is I get 'Unable to set the values property of the series class' error when setting SeriesCollection(2), collection(1) works OK, I have tried setting SeriesCollection(2) to the range xV which I know works for SeriesCollection(1), so nothing wrong with the range. SeriesCollection(2) does exist in the chart and if I set the chart data range manually it works OK! I am obviously not applying the range correctly - please help. Dim mCh as ChartObject Dim xV as Range, xX as Range, xT as Range For Each mCh in ChartObjects ' code that sets xV, xX and xT removed for clarity mCh.Activate ActiveChart.SeriesCollection(1).Values = xV ActiveChart.SeriesCollection(1).XValues = xX ActiveChart.SeriesCollection(2).Values = xT '<==== error on this line Next -- Regards, Nigel |
Chart update using VBA
That's not it; it worked for series 1. If xT is actually defined as a valid
range, then .Values = xT should set the values to the range, without needing the intermediate formula construction. What's the chart type? If it's a line or XY chart, does series 2 have any valid data in its .Values property to begin with? If not, you'll get this error. You must either make sure that the series already has valid data (i.e., points plotted), or convert the series temporarily to another type (e.g., area or column). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... Assuming xT refers to a suitable range in a single row or column of cells, and the chart contains two series, maybe the data when converted to an array like this "{1 ,2, 3}" is more then 255 characters incl. brackets, commas & spaces Normally it's more convenient to link to data in cells, you could re-write the series formula or try this s = "=" & xT.Address(, , xlR1C1, True) ActiveChart.SeriesCollection(2).Values = s Regards, Peter T "Nigel" wrote in message ... I have some code that set the range for two series charts on one worksheet. The charts are embedded on a worksheet, and the ranges xV, xX and xT are set within the code and are valid references pointing to the data required. My problem is I get 'Unable to set the values property of the series class' error when setting SeriesCollection(2), collection(1) works OK, I have tried setting SeriesCollection(2) to the range xV which I know works for SeriesCollection(1), so nothing wrong with the range. SeriesCollection(2) does exist in the chart and if I set the chart data range manually it works OK! I am obviously not applying the range correctly - please help. Dim mCh as ChartObject Dim xV as Range, xX as Range, xT as Range For Each mCh in ChartObjects ' code that sets xV, xX and xT removed for clarity mCh.Activate ActiveChart.SeriesCollection(1).Values = xV ActiveChart.SeriesCollection(1).XValues = xX ActiveChart.SeriesCollection(2).Values = xT '<==== error on this line Next -- Regards, Nigel |
Chart update using VBA
hi Jon
If I renew the xValues first it works! ActiveChart.SeriesCollection(1).XValues = xX ActiveChart.SeriesCollection(1).Values = xV ActiveChart.SeriesCollection(2).Values = xT Curious! Thanks for your help -- Regards, Nigel "Jon Peltier" wrote in message ... That's not it; it worked for series 1. If xT is actually defined as a valid range, then .Values = xT should set the values to the range, without needing the intermediate formula construction. What's the chart type? If it's a line or XY chart, does series 2 have any valid data in its .Values property to begin with? If not, you'll get this error. You must either make sure that the series already has valid data (i.e., points plotted), or convert the series temporarily to another type (e.g., area or column). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... Assuming xT refers to a suitable range in a single row or column of cells, and the chart contains two series, maybe the data when converted to an array like this "{1 ,2, 3}" is more then 255 characters incl. brackets, commas & spaces Normally it's more convenient to link to data in cells, you could re-write the series formula or try this s = "=" & xT.Address(, , xlR1C1, True) ActiveChart.SeriesCollection(2).Values = s Regards, Peter T "Nigel" wrote in message ... I have some code that set the range for two series charts on one worksheet. The charts are embedded on a worksheet, and the ranges xV, xX and xT are set within the code and are valid references pointing to the data required. My problem is I get 'Unable to set the values property of the series class' error when setting SeriesCollection(2), collection(1) works OK, I have tried setting SeriesCollection(2) to the range xV which I know works for SeriesCollection(1), so nothing wrong with the range. SeriesCollection(2) does exist in the chart and if I set the chart data range manually it works OK! I am obviously not applying the range correctly - please help. Dim mCh as ChartObject Dim xV as Range, xX as Range, xT as Range For Each mCh in ChartObjects ' code that sets xV, xX and xT removed for clarity mCh.Activate ActiveChart.SeriesCollection(1).Values = xV ActiveChart.SeriesCollection(1).XValues = xX ActiveChart.SeriesCollection(2).Values = xT '<==== error on this line Next -- Regards, Nigel |
Chart update using VBA
It's more of an art than a science.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Nigel" wrote in message ... hi Jon If I renew the xValues first it works! ActiveChart.SeriesCollection(1).XValues = xX ActiveChart.SeriesCollection(1).Values = xV ActiveChart.SeriesCollection(2).Values = xT Curious! Thanks for your help -- Regards, Nigel "Jon Peltier" wrote in message ... That's not it; it worked for series 1. If xT is actually defined as a valid range, then .Values = xT should set the values to the range, without needing the intermediate formula construction. What's the chart type? If it's a line or XY chart, does series 2 have any valid data in its .Values property to begin with? If not, you'll get this error. You must either make sure that the series already has valid data (i.e., points plotted), or convert the series temporarily to another type (e.g., area or column). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Peter T" <peter_t@discussions wrote in message ... Assuming xT refers to a suitable range in a single row or column of cells, and the chart contains two series, maybe the data when converted to an array like this "{1 ,2, 3}" is more then 255 characters incl. brackets, commas & spaces Normally it's more convenient to link to data in cells, you could re-write the series formula or try this s = "=" & xT.Address(, , xlR1C1, True) ActiveChart.SeriesCollection(2).Values = s Regards, Peter T "Nigel" wrote in message ... I have some code that set the range for two series charts on one worksheet. The charts are embedded on a worksheet, and the ranges xV, xX and xT are set within the code and are valid references pointing to the data required. My problem is I get 'Unable to set the values property of the series class' error when setting SeriesCollection(2), collection(1) works OK, I have tried setting SeriesCollection(2) to the range xV which I know works for SeriesCollection(1), so nothing wrong with the range. SeriesCollection(2) does exist in the chart and if I set the chart data range manually it works OK! I am obviously not applying the range correctly - please help. Dim mCh as ChartObject Dim xV as Range, xX as Range, xT as Range For Each mCh in ChartObjects ' code that sets xV, xX and xT removed for clarity mCh.Activate ActiveChart.SeriesCollection(1).Values = xV ActiveChart.SeriesCollection(1).XValues = xX ActiveChart.SeriesCollection(2).Values = xT '<==== error on this line Next -- Regards, Nigel |
| All times are GMT +1. The time now is 10:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com