Finding precise data points on series

[XEVEN__]

I want to label a precise data point on my line chart. Every time I hover over the area I get the dates before or after instead of the exact date I want to label as the chart/lines are representing over twenty years of daily data you can imagine trying to zero in on a specific date/value. I am using excel 365. Is there another way to select the exact date I want to label instead of spending forever trying to hover over it and make it appear?

 

[NdNoviceHlp]

Hi XEVEN_ Here is a vba solution. You will need to adjust the sheet name, chart number (if needed), seriescollection number and "YourDate" to suit. HTH. Dave

Sub test()
Dim Cnt As Long, YourDate As Date, Flag As Boolean
YourDate = Date '****** enter search date here
For Cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, Cnt) = YourDate Then
Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(Cnt).DataLabel.Select
Flag = True
Exit For
End If
Next Cnt
If Flag Then
MsgBox "No date found"
End If
End Sub

Function GetYLabel(Isrs As Long, ipt As Long) As Double
' Isrs is series number, ipt is point number
Dim s As String, cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
s = GetYValue(cht, Isrs, ipt)
GetYLabel = s
End Function

ps. run the test sub to operate

 

[NdNoviceHlp]

Whoops. Missed the edit. Dave

Sub test()
Dim Cnt As Long, YourDate As Date, Flag As Boolean
YourDate = Date '****** enter search date here
For Cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, Cnt) = YourDate Then
Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(Cnt).DataLabel.Select
Flag = True
Exit For
End If
Next Cnt
If Not Flag Then
MsgBox "No date found"
End If
End Sub

Function GetYLabel(Isrs As Long, ipt As Long) As Double
' Isrs is series number, ipt is point number
Dim s As String, cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
s = GetYValue(cht, Isrs, ipt)
GetYLabel = s
End Function

 

[XEVEN__]

Whoops. Missed the edit. Dave

Sub test()
Dim Cnt As Long, YourDate As Date, Flag As Boolean
YourDate = Date '****** enter search date here
For Cnt = 1 To Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points.Count
If GetYLabel(1, Cnt) = YourDate Then
Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(Cnt).DataLabel.Select
Flag = True
Exit For
End If
Next Cnt
If Not Flag Then
MsgBox "No date found"
End If
End Sub

Function GetYLabel(Isrs As Long, ipt As Long) As Double
' Isrs is series number, ipt is point number
Dim s As String, cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
s = GetYValue(cht, Isrs, ipt)
GetYLabel = s
End Function

Thanks Dave! Sorry for the delayed response. I am only able to work on my charts periodically. I'm just learning this VBA. It is pretty cool so far. Very finicky but likely helpful of I cant figure it out.

Regards

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave