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

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

user1641
1#
user1641 Published in July 18, 2018, 4:47 am

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO