Labeling last data points across multiple graphs and series

Question

I'm currently trying to add a data label to only the last point of each series in each graph on a worksheet. I'm currently adapting one of the solutions proposed here: "https://superuser.com/questions/1285179/adding-data-label-only-to-the-last-value".

However, my code keeps on popping up with the error: "Runtime error '13', type mismatch". When I go in debug mode, it higlights the line "Set chrt = ws.ChartObjects(Chart_Name)".

Sub LastDataLabel()


Dim Chart_Name As String
Dim i, Total_Charts, Total_Series As Integer

Dim ws As Worksheet
Dim chrt As Chart
Dim srs As Series
Dim pnt As Point
Dim p As Integer

Application.ScreenUpdating = False

Set ws = ActiveSheet

Total_Charts = Range("C12").Value

For i = 1 To Total_Charts
    Chart_Name = ActiveSheet.Cells(14 + i, 2).Value 'A list of all chart names exists along this range
    Set chrt = ws.ChartObjects(Chart_Name)
    Total_Series = chrt.SeriesCollection.Count

    For j = 1 To Total_Series
        Set srs = chrt.SeriesCollection(j)

        srs.ApplyDataLabels

        For p = 1 To srs.Points.Count - 1
            Set pnt = srs.Points(p)

            pnt.DataLabel.Text = ""
        Next

        srs.Points(srs.Points.Count).DataLabel.Format.TextFrame2.TextRange.Font.Size = 10
        srs.Points(srs.Points.Count).DataLabel.Format.TextFrame2.TextRange.Font.Name = "Arial"
    Next j
Next i



End Sub

Any and all help is greatly appreciated. Thank you!

What is the value of ActiveSheet.Cells(14 + i, 2).Value at the time of error? — Siddharth Rout
– Siddharth Rout, Commented Aug 23, 2019 at 19:39
Set chrt = ws.ChartObjects(Chart_Name).Chart ChartObject is just a container shape for the chart - it's not the actual chart itself. — Tim Williams
– Tim Williams, Commented Aug 23, 2019 at 19:55
@TimWilliams, Thank you. The macro is now running without bugs! If you post your comment as a proposed solution, I'll be happy to verify it as the correct answer. — Jr Analyst
– Jr Analyst, Commented Aug 23, 2019 at 20:03

Tim Williams · Accepted Answer · 2019-08-23 20:17:29Z

1

ChartObject is just a container shape for the chart - it's not the actual chart itself

This should improve things:

Set chrt = ws.ChartObjects(Chart_Name).Chart

answered Aug 23, 2019 at 20:17

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Labeling last data points across multiple graphs and series

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related