chart point value
 

Hi all

How can I address a specific data point on a chart to give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value)

chart point value
 

Claude,

Does the code below do what you want? The referencing of the chart depends
upon whether it is a standalone chart sheet or an embedded chart on a
worksheet.

Troy

Sub WorksheetEmbeddedChart()
Dim ws As Worksheet
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
'Note: the name of the chart is: "Chart(space)1".
Set cht = ws.ChartObjects("Chart 1").Chart
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


Sub ChartSheet()
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

'Note: the name of the chart is: "Chart1".
Set cht = ThisWorkbook.Charts("Chart1")
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


"Claude" wrote in message
...
Hi all

How can I address a specific data point on a chart to give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value)

chart point value
 

I think it's easiest if you first assign all the values to an array,
then use the array to access the individual values.

eg.

Dim i As Integer
Dim v As Variant

v = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Values

For i = 1 To UBound(v)
MsgBox v(i)
Next i


Regards,
Vic Eldridge


"Claude" wrote in message ...
Hi all

How can I address a specific data point on a chart to give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value)

chart point value
 

Well now I'm confused.
Check out this little macro. The first MsgBox works but the second one fails.
She's a fussy old thing hey ?

Sub Huh()
Dim srs As Series
Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
MsgBox srs.Values(1)
MsgBox ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Values(1)
End Sub

Regards,
Vic Eldridge



"TroyW" wrote in message ...
Claude,

Does the code below do what you want? The referencing of the chart depends
upon whether it is a standalone chart sheet or an embedded chart on a
worksheet.

Troy

Sub WorksheetEmbeddedChart()
Dim ws As Worksheet
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
'Note: the name of the chart is: "Chart(space)1".
Set cht = ws.ChartObjects("Chart 1").Chart
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


Sub ChartSheet()
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

'Note: the name of the chart is: "Chart1".
Set cht = ThisWorkbook.Charts("Chart1")
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


"Claude" wrote in message
...
Hi all

How can I address a specific data point on a chart to give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value)

chart point value
 

Hi Vic

Sorry for the late reply. The idea with first adding the
values to an array is a simple solution that works, thanks!

Thanks also to Troy for pointing out the XValues(i) syntax
(also I was struggling to implement it: maybe it's because
I'm still using excel97...)


-----Original Message-----
Well now I'm confused.
Check out this little macro. The first MsgBox works but
the second one fails.
She's a fussy old thing hey ?

Sub Huh()
Dim srs As Series
Set srs = ActiveSheet.ChartObjects
(1).Chart.SeriesCollection(1)
MsgBox srs.Values(1)
MsgBox ActiveSheet.ChartObjects
(1).Chart.SeriesCollection(1).Values(1)
End Sub

Regards,
Vic Eldridge



"TroyW" wrote in message
...
Claude,

Does the code below do what you want? The referencing
of the chart depends
upon whether it is a standalone chart sheet or an
embedded chart on a
worksheet.

Troy

Sub WorksheetEmbeddedChart()
Dim ws As Worksheet
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
'Note: the name of the chart is: "Chart(space)1".
Set cht = ws.ChartObjects("Chart 1").Chart
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


Sub ChartSheet()
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

'Note: the name of the chart is: "Chart1".
Set cht = ThisWorkbook.Charts("Chart1")
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


"Claude" wrote in
message
...
Hi all

How can I address a specific data point on a chart to
give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points
(1).Value)

.

chart point value
 

But this line works in place of your second:

MsgBox WorksheetFunction.Index(ActiveSheet.ChartObjects(1 ) _
.Chart.SeriesCollection(1).Values, 1)

Go figure.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Vic Eldridge wrote:

Well now I'm confused.
Check out this little macro. The first MsgBox works but the second one fails.
She's a fussy old thing hey ?

Sub Huh()
Dim srs As Series
Set srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
MsgBox srs.Values(1)
MsgBox ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Values(1)
End Sub

Regards,
Vic Eldridge



"TroyW" wrote in message ...

Claude,

Does the code below do what you want? The referencing of the chart depends
upon whether it is a standalone chart sheet or an embedded chart on a
worksheet.

Troy

Sub WorksheetEmbeddedChart()
Dim ws As Worksheet
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
'Note: the name of the chart is: "Chart(space)1".
Set cht = ws.ChartObjects("Chart 1").Chart
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


Sub ChartSheet()
Dim cht As Chart
Dim ser1 As Series
Dim ii As Integer

'Note: the name of the chart is: "Chart1".
Set cht = ThisWorkbook.Charts("Chart1")
Set ser1 = cht.SeriesCollection(1)
ii = 2
MsgBox "Series1(" & ii & "): x=" & _
ser1.XValues(ii) & ", y=" & ser1.Values(ii)
End Sub


"Claude" wrote in message
.. .

Hi all

How can I address a specific data point on a chart to give
back the specific (y)value?

the following does not work:
MsgBox (ActiveChart.SeriesCollection(1).Points(1).Value)