Home How can I access a single cell from a range of cells stored in a series.xvalues?
Reply: 1

How can I access a single cell from a range of cells stored in a series.xvalues?

pariscraigm Published in 2017-12-07 17:25:52Z

The backstory of why I'm trying to do this is because I need to place callout labels at the peaks of local maximas, but the values need to be the x-values and not the y-values.

I have tried doing this multiple way, most recently like this:

Dim c As Chart

Set c = sht.Shapes.AddChart.Chart

With c
    .ChartType = xlXYScatterLinesNoMarkers
    .SeriesCollection(8).Name = "Data Labels"
    .SeriesCollection(8).XValues = Range(sht.Cells(start, 2), sht.Cells(ender, 2))
    .SeriesCollection(8).Values = Range(sht.Cells(start, 9), sht.Cells(ender, 9))
    .SeriesCollection(8).Format.Line.Visible = msoFalse
End With

For i = 1 To c.SeriesCollection(8).Points.count

    c.SeriesCollection(8).Points(i).HasDataLabel = True
    c.SeriesCollection(8).Points(i).DataLabel.Position = xlLabelPositionAbove
    c.SeriesCollection(8).Points(i).DataLabel.Format.AutoShapeType = msoShapeRectangularCallout
    c.SeriesCollection(8).Points(i).DataLabel.Format.Line.Visible = msoTrue
error--->c.SeriesCollection(8).Points(i).DataLabel.Text = c.SeriesCollection(8).XValues.Cells(i, 1)

Next i

This returns an error "Object Required"

I have also tried

c.SeriesCollection(8).Points(i).DataLabel.Text = c.SeriesCollection(8).XValues(i)
c.SeriesCollection(8).Points(i).DataLabel.Text = c.SeriesCollection(8).XValues(i).value

And A few more that I am most likely forgetting off of the top of my head.

What I really want and need is to retrieve the value located in the "i" position of c.SeriesCollection(8).XValues

Thanks for any help.

Tim Williams
Tim Williams Reply to 2017-12-07 17:45:39Z

XValues returns a Variant array (not a Range), but for some reason this throws an error:

Debug.Print ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues(1)

Run-time error '451':Property let procedure not defined and property get procedure did not return an object

Whereas this works just fine:

Sub Tester()

    Dim v, i
    v = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues

    For i = LBound(v) To UBound(v)
        Debug.Print "Point#" & i, v(i)
    Next i

End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.33939 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO