Return the location of data point on a chart

[Guest]

Using vba for excel, how do I return the location of a specific data point in
Excel. For example, say I have 3 data points on a line graph. I want to
know the coordinates of the 2nd point. I need something that will tell me it
is located at coordinates (248.25, 58.5, 248.25, 205.5).

thanks!

 

[Jon Peltier]

ND -

You can use algebra to determine the point's coordinates based on its values, on the
axis scales, and the coordinates of the chart's plot inside area. In this post, I
show how to draw a shape that simulates an area chart, except it fills in the area
to the left of an XY scatter series:

http://groups-beta.google.com/group..._frm/thread/9e971e449deed969/8940c814427f3afa

This one explains how to draw a filled shape connecting the points in an XY series:

http://groups-beta.google.com/group...e_frm/thread/2058c388294e03a/99b7c8736b5852c3

What will you use these coordinates for?

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

 

[Guest]

Thank you sir! I have made some good progress on my project by looking at
the examples that are posted in your links. What I am trying to do is this:

I have a linegraph that shows frequencies over time. When there is a phase
change in my experiment, I break the line between two data points (I can do
that). I then insert a drawing of a line by saying something like
"ActiveChart.Shapes.AddLine(133.5, 51#, 133.5, 207#).Select", thus indicating
a phase change. The problem is that when I add new data points, the
linegraph scrunches together more, and the line does not move
correspondingly. I need my phase change line to move when the data points
move. I was thinking that if there was some command in vba like
mySrs.datapoints(2).location that would return (133.5, 51#, 133.5, 207#).
Then I could tell it to move my phase line accordingly.

The examples you gave are a good start for me. I'm a rather elementary
programmer, so I will probably have to spend some time tinkering around with
this until it works. If there is something super-easy, I would appreciate
any additional tips!

Thanks for your help!
Neill

 

[Peter T]

Could you include a second line series based on similar data. Perhaps with
no markers, make unwanted points invisible as & when, colour formatted to
suit.

Just a thought, Jon may well have a better suggestion.

Regards,
Peter T

 

[Peter T]

Could you include a second line series based on similar data. Perhaps with
no markers, make unwanted points invisible as & when, colour formatted to
suit.

Just a thought, Jon may well have a better suggestion.

Regards,
Peter T

 

[Jon Peltier]

Peter -

Actually, I was going to suggest exactly that. A new series is tied to the X and Y
values of the chart axis coordinates, not of the chart object, so it will move when
the axis is rescaled.

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