PDA

View Full Version : How to Extract Row reference from chart series



DannyZuco
04-15-2014, 04:19 AM
I would like to extract the row a chart series references.

So for the following series/chart the row reference would be 172

=SERIES("variable1",Data!$CE$5:$CJ$5,Data!$CE$172:$CJ$172,4)

In the case below I would want to bring back 17

=SERIES(Data!$B$17,Data!$BG$5:$BL$5,Data!$AO$17:$AZ$17,6)

Is there a quick way to do this? I have tried something like this which works for the first example but not the second.

txt = .Chart.SeriesCollection(i).Formula
Row = Mid(Split(txt, ",")(2) & vbCr, 10, 3)
msgbox row

Thanks Danny

Bob Phillips
04-15-2014, 05:55 AM
How about tis


txt = .ChartObjects(1).Chart.SeriesCollection(i).Formula
ary = Split(txt, "$")
aryTop = UBound(ary)
seriesRow = Left$(ary(aryTop), InStr(ary(aryTop), ",") - 1)
MsgBox seriesRow

DannyZuco
04-15-2014, 10:41 AM
Thanks XLD. That seem to do the trick.

Thanks,

D