Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to return the address of the range of plotted data on an XY scatter chart?
I can't figure out the VBA code that will return the address of the
range of data plotted on an embedded chart on say sheet1. Everything I try just results in the "object does not support this property or method" error message. I've been recording macros for clues as well as studying the Object Browser, all to no avail. Any clues? Ken Johnson |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to return the address of the range of plotted data on anXY scatter chart?
Hi,
To get the ranges used by a chart data series use the formula property. Use this in the immediate window. ?activechart.SeriesCollection(1).formula =SERIES(,Sheet1!$A$1:$A$5,Sheet1!$B$1:$B$5,1) See John Walkenbach's page on using a classobject to parse the SERIES formula. http://j-walk.com/ss/excel/tips/tip83.htm Cheers Andy Ken Johnson wrote: I can't figure out the VBA code that will return the address of the range of data plotted on an embedded chart on say sheet1. Everything I try just results in the "object does not support this property or method" error message. I've been recording macros for clues as well as studying the Object Browser, all to no avail. Any clues? Ken Johnson -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to return the address of the range of plotted data on an XY scatter chart?
Hi Andy, Thank you very much for that information. I can see now why I was having so much trouble. Ken Johnson |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to return the address of the range of plotted data on an XY scatter chart?
Hi Andy,
When I type this in the Immediate Window... sheet1.ChartObjects("Chart 1").activate ?activechart.SeriesCollection(1).formula the returned value is.. =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2: $B$11,1) which is fine. I thought I could skip the activate step so I tried... ?sheet1.ChartObjects("Chart 1").SeriesCollection(1).formula which did not work. Is it therefore true to say that with embedded charts the code must activate the chart before the SeriesCollection can be accessed, or am I doing something wrong? Ken Johnson |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to return the address of the range of plotted data on anXY scatter chart?
No need to activate, just need the Chart part of the OM.
?sheet1.ChartObjects("Chart 1").chart.SeriesCollection(1).formula Cheers Andy Ken Johnson wrote: Hi Andy, When I type this in the Immediate Window... sheet1.ChartObjects("Chart 1").activate ?activechart.SeriesCollection(1).formula the returned value is.. =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2: $B$11,1) which is fine. I thought I could skip the activate step so I tried... ?sheet1.ChartObjects("Chart 1").SeriesCollection(1).formula which did not work. Is it therefore true to say that with embedded charts the code must activate the chart before the SeriesCollection can be accessed, or am I doing something wrong? Ken Johnson -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to return the address of the range of plotted data on an XY scatter chart?
Thanks again Andy. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stock High-Low-Close Chart | Charts and Charting in Excel | |||
Dynamic column chart - auto sort on data range | Charts and Charting in Excel | |||
Scatter Chart Data Point Highlighting | Charts and Charting in Excel | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |