Hello. I am trying to assign labels to a bubble chart based on a filtered range of data. I have leveraged the code below from Jon Peltier to do just that. However, in my use-case the code only returns the label for the first data point in the series, and fails to loop after that. I can't figure out why. It fails to loop the source range whether there are hidden rows or not. The source range is a table and sits in a worksheet different than the chart.
Any help would be much appreciated! Thanks!
Any help would be much appreciated! Thanks!
VBA Code:
Sub AttachLabelsToBubbles()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
Dim lngChtCounter As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Profile")
ws.ChartObjects("ProfileChart").Activate
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
With ActiveChart.SeriesCollection(1)
For Counter = 1 To Range(xVals).Cells.Count
If Not Range(xVals).Cells(Counter, 1).EntireRow.Hidden Then
lngChtCounter = lngChtCounter + 1
.Points(lngChtCounter).HasDataLabel = True
.Points(lngChtCounter).DataLabel.Text = Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
End If
Next Counter
End With
End Sub