First Macro experience... fell at the first hurdle.

[henryhad]

I am trying to label points on a scatter graph that are not the X or Y value. Microsoft supplied this Macro, which I am sure is very helpful but I cannot figure out how to alter it to fit me spreadsheet:

Sub AttachLabelsToPoints() 'Dimension variables. Dim Counter As Integer, ChartName As String, xVals As String ' Disable screen updating while the subroutine is run. Application.ScreenUpdating = False '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. For Counter = 1 To Range(xVals).Cells.Count ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _ True ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _ Range(xVals).Cells(Counter, 1).Offset(0, -1).Value Next CounterEnd Sub

This macro attaches labels from cells A2:A6. I would like to attach labels from cells E40:E79. My X Values are in F40:F79 and my Y values are in G40:G79.

Can somebody help me to understand how to locate the bit of Macro I need to change and why I change it to your suggestion. I thought I would be able to see somewhere in the Macro "A2" and simply change this to "E40". I also thought this bit might be the key, " Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))" but why 9?

I completely understand I am asking for a lot in terms of teaching, but whenever I get stuck on Excel it always seems Macro is the easiest solution so I am very ready to learn!

 

[xladept]

Hi henryhad,

One heck of a first experience - I took out the comments - and arranged the code - the series collection number may hold your answer:

Sub AttachLabelsToPoints()
Dim Counter As Integer, ChartName As String, xVals As String
Application.ScreenUpdating = False
xVals = ActiveChart.SeriesCollection(1).Formula
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
For Counter = 1 To Range(xVals).Cells.count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub