+ Reply to Thread
Results 1 to 6 of 6

The number of a data point in a series

Hybrid View

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    Hoganas
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question The number of a data point in a series

    Assume this:
    I have plotted XY data in a chart. Call the value series 'MySeries', and assume its "SeriesCollection" number is 20.
    If, in a VisualBasic macro, I do this:

    Sheets("MyGraphs").Select
    ActiveChart.SeriesCollection(20).Points(84).Select
    the 84-th point in 'MySeries' in the chart on sheet 'MyGraphs' is selected.

    But how do I do "the reverse"?
    I.e. assume I select a point in the plot of 'MySeries' on the chart on sheet 'MyGraphs', how do I get the number in the data series 'MySeries'?
    Assume the sheet "MyData" contains 3 columns:
    The X-values of 'MySeries'
    The Y-values of 'MySeries'
    A comment to each XY pair
    Let's assume I have created a button in or close to the graph area. I want the service handler to this button, in sheet "MyGraphs",
    to select sheet "MyData" and the comment cell on the row where the X and Y for the selected point are.
    This will require a function we could call


    getNumberInSeries(SelectedPt as Point) As Integer
    The procedure would be like (of course there is "Dim SelectedPoint As Point", "Dim PointNumber As Integer" in the declaration section):
       Set SelectedPoint = Selection
       PointNumber = getNumberInSeries(SelectedPoint)
       ...
    What could 'getNumberInSeries' look like?
    Last edited by jeffreybrown; 10-16-2012 at 09:26 AM. Reason: Please use code tags...Thanks.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: The number of a data point in a series

    use SelectedPoint.Name, which return something like S1P1 series index and point index.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    10-16-2012
    Location
    Hoganas
    MS-Off Ver
    Excel 2003
    Posts
    5

    Thumbs up Re: The number of a data point in a series

    Thank you, Andy

    /dindea

  4. #4
    Registered User
    Join Date
    10-16-2012
    Location
    Hoganas
    MS-Off Ver
    Excel 2003
    Posts
    5

    Smile Re: The number of a data point in a series

    (Andy,)

    ... Only that it should (certainly) be:
    Dim SelectedPoint as Point
    Dim SeiesAndPointnumber As String
    
    SelectedPoint = Selection
    SeiesAndPointnumber = SelectedPoint.DataLabel.Name
    You had missed '.DataLabel".
    'SeriesAndPointnumber' takes on a value like
    "Text S16P211".

    /dindea
    Extracting the point number as an Integer is "peanuts"...
    Last edited by dindea; 10-17-2012 at 06:08 AM. Reason: typo

  5. #5
    Registered User
    Join Date
    10-16-2012
    Location
    Hoganas
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: The number of a data point in a series

    I "said too much".
    The approach with "SelectedPoint.DataLabel.Name" works only when the point has a data label (SelectedPoint.HasDataLabel = True).
    For a point without a data label (SelectedPoint.HasDataLabel = False) it does not work. "SelectedPoint.DataLabel.Name" results in the error "Unable to get the Name property ..."
    (and SelectedPoint.DataLabel.Name = <Unable to get the Name property ...>).
    So, how do I do it on a point without a data label?

    "SelectedPoint.Name" yields the error "Object variable or With block variable not set".

    /dindea
    Last edited by dindea; 10-17-2012 at 07:33 AM. Reason: additional info

  6. #6
    Registered User
    Join Date
    10-16-2012
    Location
    Hoganas
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: The number of a data point in a series

    It turned out that the problem was Excel2003. It works in Excel2010. And it is possible to get the coordinates of a point in a series without "messing with" algebra.
    'SelectedPoint.Left' and 'SelectedPoint.Top' return the X and Y coordinates (N.B., the Y coordinate is counted from the top edge) of the point, in chart points.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1