asked on # Get the Y value of a line at X in a chart in VBA

How can I get the Y value of a line in VBA if I know the X value. I need the value of the line that excel creates because of the curves excel adds when it smoothes the lines between points.

Thank you,

Randy

Thank you,

Randy

Microsoft OfficeVB ScriptVBAMicrosoft ExcelVisual Basic Classic

Looking at the attached file you can see 4 lines. I will be given a single point. What I need to do is figure out if that point is inside those 4 lines.

Thank you.

GRT3.xlsx

Thank you.

you mean: You will be given an X,Y reference and you want to know if it is inside the 4 lines or outside. right ? meaning you need to input an X and a Y and you want the macro to tell you if YES inside or NO outside ?

gowflow

gowflow

That is correct. It wouldn't be so bad if it were a square...but of course it's not.

... working on it

Meantime I see your initial question: How can I knowY if I give it X .... then you say I need to know if the point falls inside the graph ....

so which one is it ? as your graph is abit weired.

How about if you give a point reference (20,54.2) and we show it to you on the graph would this be fine ??

gowlfow

Meantime I see your initial question: How can I knowY if I give it X .... then you say I need to know if the point falls inside the graph ....

so which one is it ? as your graph is abit weired.

How about if you give a point reference (20,54.2) and we show it to you on the graph would this be fine ??

gowlfow

I can add points. If I have the X value and can get the Y value I can probably work the rest out myself. I don't know how to get the Y value of the line itself when I know X

ok I can get you this also, but first examine this graph you have 2 values fill them and see how they display in the graph and below code does that.

Once I get your comments will take it from there.

Pls use attached workbook.

gowflow

GRT3.xlsm

Once I get your comments will take it from there.

```
Option Explicit
Sub CheckPoint()
Dim WS As Worksheet
Dim cChart As ChartObject
Dim Sr As Series
Dim Pt As Point
Dim I As Long
Set WS = ActiveSheet
For Each cChart In WS.ChartObjects
For Each Sr In cChart.Chart.SeriesCollection
If Sr.Name = "InputValue" Then
Sr.Values = Range("O36")
Sr.XValues = Range("O37")
For Each Pt In Sr.Points
Pt.MarkerBackgroundColor = 3
Pt.MarkerSize = 7
Next Pt
End If
Next Sr
Next cChart
End Sub
```

Pls use attached workbook.

gowflow

Yes, I see the point that has been added. As I said, I could already do that. I would like a macro that can tell me if it inside the "box" or not but I would first like to know the Y value of the lines at X. From that I think I can work out where the point is.

ok here is the added one where you input X and it tell you if Y exist on one of the Graphs it give you its value if not then you get NO. This is entered in the second square in S36.

Check it.

here is the code

Chk the WB it has both now.

gowlfow

GRT3-V01.xlsm

Check it.

here is the code

```
Function FindY(X As Double) As String
Dim WS As Worksheet
Dim cChart As ChartObject
Dim Sr As Series
Dim Pt As Point
Dim I As Long
Dim bFound As Boolean
Set WS = ActiveSheet
For Each cChart In WS.ChartObjects
For Each Sr In cChart.Chart.SeriesCollection
If Sr.Name <> "InputValue" Then
For I = LBound(Sr.XValues) To UBound(Sr.XValues)
If Sr.XValues(I) = X Then
FindY = Sr.Values(I)
bFound = True
Exit Function
End If
Next I
End If
Next Sr
Next cChart
FindY = "NO"
End Function
```

Chk the WB it has both now.

gowlfow

It looks like it will only find the Y value if X exists at one of the points on the line. I need to find Y anywhere on the line where it will intersect with X. Not the data points. In other words, ignore the data points and work with the line itself.

what line I don't understand what you want.

gowflow

gowflow

We can use the line on the vertical line on the left. If X were 25, the Y looks to be roughly 21 on that line. I need to get that Y value.

mmm I see let me work on something

gowflow

gowflow

The X is the horizontal line and the Y is the vertical !

Right. If X=25, What is the value of Y on the left-most vertical line...

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

That still doesn't tell me if the point is inside the "box". I made a new spreadsheet with many more data points that form the "box" using only 1 line. Maybe this will be more useful?

GRT32.xlsx

Sorry don't want to be out of focus here but your question clearly say:

and for this I have clearly answered it in my previous post which should close this question.

Now if you have an other query, then by all mean do let me know and I will be glad to assist.

gowflow

How can I get the Y value of a line in VBA if I know the X value. I need the value of the line that excel creates because of the curves excel adds when it smoothes the lines between points.

and for this I have clearly answered it in my previous post which should close this question.

Now if you have an other query, then by all mean do let me know and I will be glad to assist.

gowflow

Yes, I asked for the value of the line ("How can I get the Y value of a line") where X will intersect with it. You gave me the value of the nearest POINT on the line which I already could do my self. I appreciate your assistance but I am no further than when I started and the question is still not answered.

Thank you anyway,

Randy

Thank you anyway,

Randy

So I don't get it !!!!

Can you explain why you need to do all this maybe I could understand ? maybe there is an other way ?

gowflow

Can you explain why you need to do all this maybe I could understand ? maybe there is an other way ?

gowflow

If you do have a solution please post it to the question here, that way if someone else searches for it in the future they will find a useful solution.

If you don't want to do that for any reason then I will move to delete the question, since we try not to archive questions that were never solved.

If you don't want to do that for any reason then I will move to delete the question, since we try not to archive questions that were never solved.

Great effort and tenacity on your part.

»

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Not sure if this is desired results or not, but try flow 415, head 1. The point is clearly outside the line, but the macro seems to think it's inside and says the pump meets that spec.

*~bp*

Tks your comment Bill Prew much appreciated.

Just a side note here, we in EE are not chasing points in the contrary we are here to learn and to benefit other from the little we know. In the course of doing so we meet people who appreciate and our satisfaction most of all comes when we know we have made a difference and helped others doing things they thought was not possible. Not always we can see a solution and sometimes asker knows better what they want, and the more askers are able to clearly state what they want in a rational clear way the easier it gets to finding the proper solution.

At then end, although we don't see each others, but mysteriously something stays, and when we are nice life bring it back at us in a way we never expected.

I am glad you were able to find the correct macro that did what you expected and what you were wanting to have. As I said its all about appreciation for the time we spend here.

Regards

gowflow

Just a side note here, we in EE are not chasing points in the contrary we are here to learn and to benefit other from the little we know. In the course of doing so we meet people who appreciate and our satisfaction most of all comes when we know we have made a difference and helped others doing things they thought was not possible. Not always we can see a solution and sometimes asker knows better what they want, and the more askers are able to clearly state what they want in a rational clear way the easier it gets to finding the proper solution.

At then end, although we don't see each others, but mysteriously something stays, and when we are nice life bring it back at us in a way we never expected.

I am glad you were able to find the correct macro that did what you expected and what you were wanting to have. As I said its all about appreciation for the time we spend here.

Regards

gowflow

Well that's not the desired result but that's because the data points are rounded to the nearest whole number in order to find a match to the whole numbers of flow and head that the user enters. I am in the process of going through the data and plotting the points at every single tick so that no rounding will need done. If you look at the data for that line, X414.23, Y17.79 and then 415.55, 18.48 it will skip 415 when those numbers are rounded. Easy enough fix.

Thanks to everyone that tried to help. I hope this solution will help someone else in the future.

-Randy

Thanks to everyone that tried to help. I hope this solution will help someone else in the future.

-Randy

@Bill useless to comment or to prove. Have this question closed and please lets get done with it. We need to focus on the positives.

gowflow

gowflow

~bp