add callout to line graph - callout has wrong date

[smak]

short description: the correct point on the graph is selected but the call out seems to be a previous point/date.

I have a line graph. when I select the last point it corresponds to 3/29. when I manually add a call out it properly shows 3/29. But when I try and use vba, the correct point on the graph will be selected but the call out says 3/25.

code that selects the 3/29 date on the graph and puts 3/25 as the call out

 ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.FullSeriesCollection(7).Points(742).Select
    ActiveChart.SetElement (msoElementDataLabelCallout)

this is a photo of the selected point is added with the vba. it selects the point that relates to row 751 and that has a date of 3/29...this is the correct point. the 8.2% that you see on the floating popup showing point 742 is the correct value that goes with 3/29

if I was to manually add the call out, it shows up like this. this is what it should look like

but when I let the rest of the code run, it gives me this call out. the code is picking a prior date for the callout

 

[RoryA]

Point 742 is not the point for row 751. That would be point 746.

 

[smak]

when I tell the code to select 746 it gives me an error. plus when hovering over the selected 742 the 8.2% is correct. if I look at the 3/25 call out it is 8.6%. this is the right value for 8/25

the issue is that there is one point on the graph selected but the call out is for a previous point that is not selected.

On a side note the range of this is from H6:H751. so there are 3 rows not in the mix. that is why it is not 746. and even when I fix the range to include rows 3-6 and then highlight 745 (since that is the new last point), it still wants to put the call out on 3/25.

I tried the 746 prior to asking this question. I have been staring at this for a while and nothing is jumping

 

[RoryA]

If your data goes from row 6 to row 751, 742 cannot be the correct point number for row 751.

 

[smak]

it looks like up till 3/8/2022 the 'point' is the date so 3/8 or 2/12. but after that it goes to a number like 734.

but I don't see a difference in the data or a range not expanded.

I agree that this numbering makes no sense. I would have expected it to be 751 or maybe even 749 if we took off the top header rows.

 

[Jon Peltier]

Try this:

ActiveChart.FullSeriesCollection(7).Points(ActiveChart.FullSeriesCollection(7).Points.Count).Select

 

[smak]

the selection code line provided works to highlighting the last point on the graph. but when I try to set the element of a callout, it does not put it on that point. it puts it on a point few earlier. I think the reason is that the last few points to do not have "values/name"

so this is my range that it is producing the chart off of. this is the x axis. the highlighted dates are mentioned below.

when I hover over the point that would relate to 3/23 I get the following data. it names the point as "3/23". this is how most of the points are labeled. the last one that has a date is 3/25.

when I hover over the last point that should be 3/29 I get the following data. it just calls it point 742 and not the data of "3/29". the 8.2% is the right percentage that corresponds to 3/29

when I try to do the call box with vba it puts it on 3/25. this is the last point that has a 'name' and not just a point number.

I think this is the issue but I don't know know to fix it. I can select/highlight the 3/29 point on the chart but when I add the callout it adds it to 3/25

 

[Jon Peltier]

I tried to replicate your data and chart. I presume those are dates in M/D format, every day starting on 3/11/2020. I don't know why some of the points show point number instead of date. Make sure the series formula includes the entire ranges for X and Y:
=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$751,Sheet1!$C$3:$C$751,1)
Although when I changed the formula to this:
=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$745,Sheet1!$C$3:$C$751,1)
the last point plotted was 3/23. So probably not your problem.
Also make sure that those last few dates haven't somehow been entered as text, though when I did this, the points were still identified by the text representation of the date, and not by point number. Also probably not your problem.

 

[smak]

thanks for looking at it. the formula you mentioned for the range is correct.

At least i have an idea why it is not working and that it is not something that I entered wrong. I will keep trying to play with it and if I come up with a solution I will put it on here in case anyone else has the same issue.

 

[Jon Peltier]

What if you recreate the data and chart in a new workbook?