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
1#
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
2#
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.
Processed in 0.33939 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO