Labelling data point on series

[viv_ien]

Hi!

I am collating some feedback which I need to present in line graphs which include the data table showing the FY and the no. of response. As such, I must use the "name" column as the series name when creating these graphs.

In addition, I have been using a VBA that I found online to label the last data point of the graphs. However, the points are labelled with the "name" column. Instead, I would like the points to be labelled with the text from the "Financial Year" column (i.e. FY13, FY14 etc).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Financial Year[/TD]
[TD]Response[/TD]
[TD]Name[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]FY13[/TD]
[TD]10[/TD]
[TD]FY13 (n = 10)[/TD]
[TD]2.2[/TD]
[TD]3.4[/TD]
[/TR]
[TR]
[TD]FY14[/TD]
[TD]20[/TD]
[TD]FY14 (n = 20)[/TD]
[TD]1.5[/TD]
[TD]3.1[/TD]
[/TR]
[TR]
[TD]FY15 1st Half[/TD]
[TD]5[/TD]
[TD]FY15 1st Half (n = 5)[/TD]
[TD]3.6[/TD]
[TD]2.8[/TD]
[/TR]
</tbody>[/TABLE]

I am completely new to VBA and have been tearing my hair out over this. Is it possible to edit the VBA below to label the data points with the data from the "financial year" column rather than the series name?

==========
Sub LabelLastPoint()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Name
End With
Next
End Sub
===========

 

[NdNoviceHlp]

Hi viv_ien and Welcome to the Board. Assuming data starts in sheet1 A2 this should work. HTH. Dave

Sub LabelLastPoint()
 Dim mySrs As Series
 Dim nPts As Long, Cnt As Integer
 Cnt = 1
 For Each mySrs In ActiveChart.SeriesCollection
 Cnt = Cnt + 1
 With mySrs
 nPts = .Points.Count
 mySrs.Points(nPts).ApplyDataLabels _
 Type:=xlDataLabelsShowValue, _
 AutoText:=True, LegendKey:=False
 mySrs.Points(nPts).DataLabel.Text = CStr(Sheets("Sheet1").Range("A" & Cnt).Value)
 End With
 Next
 End Sub

ps. please use code tags

 

[viv_ien]

Thanks for the help, Dave I managed to get it to work after tweaking the integer number.

Would just like to check if there is any way to further adjust the VBA such that it will take the data two columns left of whatever data that I have selected to be displayed on the graph?

The background is that I have multiple worksheets, which contains a few tables (and graphs) on each worksheet. Every year, I will add on new information to the table, increasing the number of rows for my tables. While I need to keep all these information, I only need to present the recent 3 years information in the graph form. As such, I will manually adjust the data selected for the graph where necessary. With the current VBA, I will have to manually adjust the integer number every year when I add new information and adjust the data selection for the graphs.

Is this possible?

 

[NdNoviceHlp]

Not sure if I understand exactly what U want (and I hate using selection) but U can trial this. Dave

Sub LabelLastPoint()
 Dim mySrs As Series, ColNum As Integer
 Dim nPts As Long, Cnt As Integer, Rng As Range
 ColNum = 0
 On Error Resume Next
 Set Rng = Selection.Offset(0, -3)
 If Err Then MsgBox "Make valid selection!": Exit Sub
 On Error GoTo 0
 ColNum = Rng.Column
 If ColNum = 0 Then
 MsgBox "Select something valid!"
 Exit Sub
 End If
 Cnt = 1
 For Each mySrs In ActiveChart.SeriesCollection
 Cnt = Cnt + 1
 With mySrs
 nPts = .Points.Count
 mySrs.Points(nPts).ApplyDataLabels _
 Type:=xlDataLabelsShowValue, _
 AutoText:=True, LegendKey:=False
 mySrs.Points(nPts).DataLabel.Text = CStr(Sheets("Sheet1").Cells(Cnt, ColNum).Value)
 End With
 Next
 End Sub