vba chart range

[Lochnagar]

Hi All,

I have a question regarding charts, and was hoping someone might be able to help.

If you have an embedded chart with a series in it, is it possible, using vba, to obtain the x or y values/range referenced, and then write that to a cell.

e.g. if the x-values for series Case 2 is

=Case_2!R3C5:R126C5
...can I get this inputed into a cell (on the worksheet, containing the embedded chart i.e. so in say, A1, you'd see Case_2!R3C5:R126C5) so I can see which range it is referencing (i.e. treat Case_2!R3C5... as a string). I appreciate that I could just click on the series in question and assuming it is on the same worksheet as the embedded chart then I'll know, but I've got several embedded charts on one worksheet, where some reference data on other worksheets within the workbook.

Any help or suggestions would be hugely appreciated.

Regards,
Lochnagar

 

[Jon Peltier]

You have to parse the series formula to get the ranges. See this trick on John Walkenbach's site:

http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/

 

[Lochnagar]

Brilliant, thanks Jon.

Regards,
Lochnagar