Consulting

Results 1 to 3 of 3

Thread: How to Extract Row reference from chart series

  1. #1

    How to Extract Row reference from chart series

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thanks XLD. That seem to do the trick.

    Thanks,

    D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •