VBA Bar Chart Value Display - Urgent!

[DomoKing]

Hi All,

So basically my chart is consisting of two sets of data and displays 2 bar graphs per x-value. So the goal is to make it so that only the last two set of bars display values on top of them, however 0 or N/A values are not displayed, I was able to achieve this through the following code:

Option Explicit

Sub Chart2_Click()
Dim mySrs As Series
Dim iPts As Long
Dim bLabeled As Boolean
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation
Else
For Each mySrs In ActiveChart.SeriesCollection
bLabeled = False
With mySrs
For iPts = .Points.Count To 1 Step -1
If bLabeled Then ' series has already received valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label if it's not the last point
mySrs.Points(iPts).HasDataLabel = False
On Error GoTo 0
Else ' series does not yet have valid label
' handle error if point isn't plotted
On Error Resume Next
' remove existing label (linked labels otherwise resist reassignment)
mySrs.Points(iPts).HasDataLabel = False

' test for 0 or #N/A (empty)
If mySrs.Values(iPts) <> 0 Then
If Not IsEmpty(mySrs.Values(iPts)) Then
' add label
mySrs.Points(iPts).ApplyDataLabels _
ShowSeriesName:=False, _
ShowCategoryName:=False, ShowValue:=True, _
AutoText:=True, LegendKey:=False
bLabeled = (Err.Number = 0)
End If
End If

' 2010 no error if point doesn't exist: label applied, but it's blank
If bLabeled Then bLabeled = (Len(mySrs.Points(iPts).DataLabel.Text) > 0)
If Not bLabeled Then
' remove blank label
mySrs.Points(iPts).HasDataLabel = False
End If
On Error GoTo 0
End If
Next
End With
Next
End If
End Sub

However the problem is that for if either one of the last two set of data have an empty value (due to 0 or N/A in the corresponding cell), then the second last set of bars will have value displayed on the bar that is on the same ajacent side as the last bar that doesn't have value on it. How do I fix this so that if either one of the last set of bar have an empty value, then it just stays as it is and no value will appear on the previous bars? Thanks a ton!

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1218719-n-a-question-for-chart.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[DomoKing]

How about help me solve the actual problem rather than keep saying I cross post..

 

[Fluff]

If you observed the rules of this board, I wouldn't have to keep mentioning that you have cross posted.

As I've never used graphs, I am unable to help.