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

Can you provide an example worksheet with a line(s) and a bit more detail on what you need?

~bp

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

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

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

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.

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

Select allOpen in new window

Pls use attached workbook.
gowflow
GRT3.xlsm

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

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

Select allOpen in new window

Chk the WB it has both now.
gowlfow
GRT3-V01.xlsm

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

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

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...

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:

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

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

@Randy,


gowflow,

---

»bp

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

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

@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