How to modify this code for adding data labels to scatter plot?

[richardcarter]

Ive been using the code below (originally from a Microsoft site) to add data labels to a scatter chart. The problem with the code is that if I have a column on my data filtered, the macro assumes that the text needed for the labels is always 898 columns to the left of whatever the filtered column is... Is there any way to 'fix' the column for the data labels? In my case they are always in column H (which is also a named ranged called "Company_Name"). Im not a VBA expert, but I was thinking I could change the last line of the code to something like .Points(lngChtCounter).DataLabel.Text = Range(xVals).Cells(Counter, 1).Range("Company_Name").Text but that is throwing up an error.


If there is any alternative code I could use for the same job (preferably one which is a bit more comprehensible to a novice!), then that will also solve my problem!

Many thanks


Sub AddLabels()
Worksheets("SG").Select
Worksheets("SG").ChartObjects(1).Select
'Attaches Labels To Points
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
Dim lngChtCounter As Long
'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, -898).Text

End If
Next Counter
End With
End Sub

 

[Andrew Poulsom]

Maybe:

.Points(lngChtCounter).DataLabel.Text = Range(xVals).Cells(Counter, 1).EntireRow.Range("H1").Text

 

[richardcarter]

That did the trick! Thank you!