Label specific datapoints "n/a"

[cubsfan05]

I have the data and the chart shown below and I'm wondering how I can display only certain data labels?
Basically, the first three companies are all n/a for sales (ignore the EBITDA columns) and so i want to label those as n/a. I tried creating a 2nd data set in column D and charting that, but it labeled all data points (D5:D12) as "0" instead of labeling datapoints D5:D7 as "n/a" and leaving the other data points without a label.

Appreciate any ideas / help you guys can offer.

 

[Domenic]

I would suggest replacing "n/a" values with "#N/A" error values. As such, theses data points will be evaluated as "Empty" instead of "0". Then we can use the following macro...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] Insert_NA_Labels()

    [COLOR=darkblue]Dim[/COLOR] MyVals [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet1").ChartObjects("Chart 4").Chart [COLOR=green]'change the sheet and chart names accordingly[/COLOR]
        MyVals = .SeriesCollection(1).Values
        [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](MyVals) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](MyVals)
            [COLOR=darkblue]If[/COLOR] IsEmpty(MyVals(i)) [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]With[/COLOR] .SeriesCollection(1).Points(i)
                    .HasDataLabel = [COLOR=darkblue]True[/COLOR]
                    .DataLabel.Text = "N/A"
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[cubsfan05]

That's perfect. I'm adding a bunch of other error checking and customized your code slightly for a couple extra loops, but thanks man. Massive help!

 

[Domenic]

You're very welcome!

 

[cubsfan05]

In case you're curious how I built out the rest code

Option Explicit

Sub ResetCharts()
    Call ResetProcedure("RevenueChart")
    Call ResetProcedure("EbitdaChart")
End Sub


Sub ResetProcedure(ChartName As String)


    Dim MyVals As Variant
    Dim i As Long
    Dim SeriesNum As Integer
    Dim AdjTotal As Integer
    Dim TransactionRow As Integer
    
    AdjTotal = WorksheetFunction.CountA(Range("B:B")) + 10
    'unhide all rows
    Sheets("Transaction Graphs").Rows("1:" & AdjTotal).EntireRow.Hidden = False
    
    For TransactionRow = 1 To AdjTotal
        If Range("B" & TransactionRow).Value = "(Invalid Identifier)" Then
            Sheets("Transaction Graphs").Range("B" & TransactionRow).EntireRow.Hidden = True
        End If
    Next TransactionRow
    
    'Clear chart labels
    ActiveSheet.ChartObjects(ChartName).Activate
    ActiveChart.SetElement (msoElementDataLabelNone)
    
    For SeriesNum = 1 To 2  'for Lincoln comps & Public transactions
        With Worksheets("Transaction Graphs").ChartObjects(ChartName).Chart
            MyVals = .SeriesCollection(SeriesNum).Values 'set loop for all values
            For i = LBound(MyVals) To UBound(MyVals) 'loop through all datapoints
                If IsEmpty(MyVals(i)) Then
                    With .SeriesCollection(SeriesNum).Points(i) 'if empty datapoint, provide "n/a" label
                        .HasDataLabel = True
                        .DataLabel.Text = "N/A"
                    End With
                End If
            Next i
        End With
    Next SeriesNum
    
    ActiveSheet.Range("A1").Select
    
End Sub