Solved: Help with labelling of graph

[EirikDaude]

Hi! I've come up with the following code to label the datapoints on a chart:
[vba]Sub test()
Call merkSistePunkt(Worksheets("Utvikling").ChartObjects("Kortsone3").Chart, "Opptak/botnsuging")
Call merkSistePunkt(Worksheets("Utvikling").ChartObjects("Kortsone3").Chart, "Nedsuging")
Call merkSistePunkt(Worksheets("Utvikling").ChartObjects("Kortsone3").Chart, "Toppsuging")
End Sub
Sub merkSistePunkt(graf As Chart, handling As String)
Dim iPts As Integer, mySrs As Series, dataOffset As Integer

Set mySrs = graf.SeriesCollection(handling)

Select Case graf.Name
Case "Kortsone3": dataOffset = 0
Case "Langsone3": dataOffset = 7
Case "Kortsone4": dataOffset = 14
Case "Langsone4": dataOffset = 21
End Select

Select Case handling
Case "Toppsuging": dataOffset = dataOffset
Case "Nedsuging": dataOffset = dataOffset + 2
Case "Opptak/botnsuging": dataOffset = dataOffset + 4
End Select

With mySrs
.HasDataLabels = True
For iPts = 1 To .DataLabels.Count
.DataLabels(iPts).Text = Worksheets("Grafverdiar").Range("B4").Offset(iPts - 1, dataOffset).Value
Next
End With
End Sub[/vba] Now, as far as I can see this code works quite well - that is I do get the datapoints labelled. However, I get a bit more info in my chart than I strictly want. If it is possible I only want the last point in each dataseries to be labelled. To complicate things further, the series don't necessarily have the same amount of "real" values. See this picture for how the graph and datavalues typically will look:

So to sum up, what I am curious to know is if there's any way to find and label just the last point of the series?

[sassora]

Hi

Something like this may help:

[VBA]With mySrs
.HasDataLabels = True
For iPts = 1 To .DataLabels.Count - 1
.DataLabels(iPts).Text = vbNull
Next
iPts = .DataLabels.Count
.DataLabels(iPts).Text = Worksheets("Grafverdiar").Range("B4").Offset(iPts - 1, dataOffset).Value
End With [/VBA]

[EirikDaude]

Thanks for the reply And sorry for taking a while to get back to this :P

While I can see what you are trying to do, it doesn't seem to work for me - the labels just get changed to 1 (with vbNull) or 0 (with vbEmpty). I assume this is because of the constants numerical values. Furthermore, the last point in the series doesn't necessarily get changed to the value I want it to be, since mySrs.DataLabels.Count always will return 8 (given the values shown in my first post), which isn't necessarily a value which is plotted in the graph (actually it is very unlikely that it is).

Any chance of some further suggestions?

[sassora]

How about attaching the workbook?

[EirikDaude]

I am reluctant to do that because it contains a lot of code which has nothing to do with the problem at hand, and because I am unsure if any of the information in it is confidential, i.e. I am not confident enough that it isn't that I am willing to post it in its entirety.

I figured the information in the pictures attached in the first post would give enough information that you at least would get an idea of where I could start attacking the problem. But since you have asked for the workbook I have whipped together one containing only the relevant information. Note that the last datapoint in each series is the one for 14/5, which doesn't show on the chart. What I want to label is the last visible datapoint.

I hope this will be of some help to figure out a way to solve the problem

[sassora]

This seems to do the trick, I've attached an amended workbook too.

[VBA]Sub test()
Call merkSistePunkt(Worksheets("Utvikling").ChartObjects("Kortsone3").Chart, "Opptak/botnsuging")
Call merkSistePunkt(Worksheets("Utvikling").ChartObjects("Kortsone3").Chart, "Nedsuging")
Call merkSistePunkt(Worksheets("Utvikling").ChartObjects("Kortsone3").Chart, "Toppsuging")
End Sub
Sub merkSistePunkt(graf As Chart, handling As String)
Dim iPts As Integer, mySrs As Series, dataOffset As Integer, FirstRow As Long, LastRow As Long

Set mySrs = graf.SeriesCollection(handling)

Select Case graf.Name
Case "Kortsone3": dataOffset = 0
Case "Langsone3": dataOffset = 7
Case "Kortsone4": dataOffset = 14
Case "Langsone4": dataOffset = 21
End Select

Select Case handling
Case "Toppsuging": dataOffset = dataOffset
Case "Nedsuging": dataOffset = dataOffset + 2
Case "Opptak/botnsuging": dataOffset = dataOffset + 4
End Select

FirstRow = 4
LastRow = Worksheets("Grafverdiar").Cells(Rows.Count, 2).Offset(, dataOffset).End(xlUp).Row

With mySrs
.HasDataLabels = True
For iPts = 1 To mySrs.DataLabels.Count
.DataLabels(iPts).Text = ""
Next iPts

For iPts = 1 To LastRow - FirstRow + 1
.DataLabels(LastRow - FirstRow + 1).Text = Worksheets("Grafverdiar").Cells(LastRow, dataOffset + 2).Value
Next iPts
End With

End Sub[/VBA]

[EirikDaude]

Yeah, it seems to work - thanks a lot for the help