Sub SelectPointsEvery12Months()
Dim ws As Worksheet
Dim chart As ChartObject
Dim series As series
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Set ws = Worksheets("LM Page") ' Change to your sheet name
Set chart = ws.ChartObjects("Chart 14") ' Change to your chart name
'Set series = chart.chart.SeriesCollection(1) ' Change to your series index
Set seriesColl = chart.chart.SeriesCollection
counter = 0
'For j = 1 To seriesColl
' Loop through each point in the series
For i = series.Points.count To 1 Step -1
counter = counter + 1
If counter = 1 Then
Select Case series.XValues(i) = DateAdd("m", -(i - 1), currentMonth)
'Select Case series
Case series.Points(i).ApplyDataLabels '1 ' For seriesColl(1)
series.Points(i).ApplyDataLabels
series.Points(i).DataLabel.Position = xlLabelPositionOutsideEnd
Case series.Points(i).ApplyDataLabels ' For seriesColl(2)
series.Points(i).ApplyDataLabels
series.Points(i).DataLabel.Position = xlLabelPositionAbove
Case series.Points(i).ApplyDataLabels ' For seriesColl(3)
series.Points(i).ApplyDataLabels
series.Points(i).DataLabel.Position = xlLabelPositionOutsideEnd
Case series.Points(i).ApplyDataLabels ' For seriesColl(4)
series.Points(i).ApplyDataLabels
series.Points(i).DataLabel.Position = xlLabelPositionCenter
Case Else
series.Points(i).ApplyDataLabels
series.Points(i).DataLabel.Position = xlLabelPositionCenter
End Select
ElseIf counter Mod 12 = 0 Then
' Increment the X value of each data point by one month
series.XValues(i) = DateAdd("m", -12, series.XValues(i))
' Update data label text
'series.Points(i).DataLabel.Text = Format(series.XValues(i), "MMMM yyyy")
series.Points(pointIndex).ApplyDataLabels
series.Points(pointIndex).DataLabel.Position = xlLabelPositionOutsideEnd
End If
Next i
Next j
End Sub
I tried Select case statement to add data labels, some how it is only working for first series with current month Index point. I need to loop it through each series for every month going back by 12 month interval (for ex: May23, May22, May21...)