CODEWITHSUNDEEP
vbaexcelexcel-formula
confused programmer
confused programmer

Reputation: 11

labels in macro

I am running a macro that I had used to create multiple line graphs that are created as an output in the next sheet. I need to add in custom data labels for the line graphs. I can't seem to get the vba for the custom labels to select (Cells(i, 10), Cells(i, 21)) as custom labels.

Upvotes: 1

Views: 1123

Answers (2)

Marcucciboy2
Marcucciboy2

Reputation: 3259

Something like this might be what you're looking for:

Sub AddCharts()

    'variable declaration
    Dim sheetSource As String
    sheetSource = "sheet5"      'update with sheet name

    Dim sheetDestination As String
    sheetDestination = "sheet6" 'update with sheet name

    With Sheets(sheetSource)
        'Find the last used row
        Dim lastRow As Long
        lastRow = .Cells(.Rows.count, "A").End(xlUp).row
    End With

    Dim i As Long
    Dim j As Long
    Dim chrt As Chart
    Dim rangeSource As Range
    Dim rangeLabels As Range
    Dim cel As Range

    'Destination sheet is selected bcoz charts will be inserted here
    Sheets(sheetDestination).Select

    'Looping from second row till last row with data
    For i = 2 To lastRow

        With Sheets(sheetSource)
            'set the chart source
            Set rangeSource = .Range(.Cells(i, "D"), .Cells(i, "O"))  'your sheet confused me on the end column

            'pick the range of labels
            Set rangeLabels = .Range(.Cells(i, "AM"), .Cells(i, "AX"))
        End With

        'Adds chart to destination sheet
        Set chrt = Sheets(sheetDestination).Shapes.AddChart.Chart

        With chrt

            'set the chart as a line chart
            .ChartType = xlLine

            'set chart source
            .SetSourceData Source:=rangeSource

            With .SeriesCollection(1)
                .name = Sheets(sheetSource).Range("Q1").Value2
                .XValues = "{""July"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"",""Jan"",""Feb"",""Mar"",""Apr"",""May"",""June""}"
                .ChartType = xlLineMarkers
            End With

            'add another series of data
            .SeriesCollection.Add Source:=Sheets(sheetSource).Range(Sheets(sheetSource).Cells(i, "P"), Sheets(sheetSource).Cells(i, "AA"))

            .SeriesCollection(2).name = Sheets(sheetSource).Range("P1").Value2

            'turns labels on
            .SeriesCollection(2).ApplyDataLabels

            j = 1
            For Each cel In rangeLabels
                .SeriesCollection(2).DataLabels(j).Text = cel.Value2
                j = j + 1
            Next cel

            .HasTitle = True

            'move the chart to left and below previous charts
            With .ChartArea
                .Left = 1
                .Top = (i - 2) * .HEIGHT
            End With

        End With

    Next

End Sub

The bit that most relevant to your changes is this:

j = 1
For Each cel In rangeLabels
    .SeriesCollection(1).DataLabels(j).Text = cel.Value2
    j = j + 1
Next cel

because it loops through the range with label text in it, (.Cells(i, "AM"), .Cells(i, "AX")) and applies those cell values as labels to the data points in order. I have not done much error handling so if there are more labels than data points it will probably break on you.

Upvotes: 1

xidgel
xidgel

Reputation: 3145

To set the name of the 5th line to say "Downtown" your code would be something like:

chrt.SeriesCollection(5).Name = "Downtown"

To make sure that your data points have labels, you can check and/or set 

chrt.SeriesCollection(5).HasDataLabels = True

To change the label of the 3rd point to "North"

chrt.SeriesCollection(5).DataLabels(3).Text = "North"

Hope that helps.

Upvotes: 1

Related Questions