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

0

There are 0 best solutions below