itipu
04-02-2010, 01:59 AM
I have a simple graph with one or more series... and I am trying to label each data poing... the following code works fine...
Dim aRng As Range
Dim srsNew As Series
Dim YValues As String
Dim XValues As String
Set aRng = Worksheets("Data").Range("C2:C30")
YValues = "=Data!R2C5:R30C5"
XValues = "=Data!R2C4:R30C4"
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.Name = rngDataSource.Cells(1, iSrsIx + 1)
.Values = YValues
.XValues = XValues
.MarkerBackgroundColorIndex = xlNone
.MarkerSize = 5
.ApplyDataLabels
With .Points
For i = 1 To .Count
With .Item(i)
.DataLabel.Text = "='" & aRng.Parent.Name & "'!" _
& aRng.Cells(i).Address(ReferenceStyle:=xlR1C1)
End With
Next i
End With
End With
However this only works well for a 1 Series Graph (i.e. see sheet Data in attached excel workbook), if you have 2 Series or more graph and have empty Y values (and so for this specific XY there is no data point) this code fails.. See sheet Data_2 or more Series..
I am wondering how to work around that but seems rather impossible from where I am..
P.S I can provide the whole routine as obviously I am seeting ranges and X&Y values dynamically.. but code above is just an example.
Dim aRng As Range
Dim srsNew As Series
Dim YValues As String
Dim XValues As String
Set aRng = Worksheets("Data").Range("C2:C30")
YValues = "=Data!R2C5:R30C5"
XValues = "=Data!R2C4:R30C4"
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.Name = rngDataSource.Cells(1, iSrsIx + 1)
.Values = YValues
.XValues = XValues
.MarkerBackgroundColorIndex = xlNone
.MarkerSize = 5
.ApplyDataLabels
With .Points
For i = 1 To .Count
With .Item(i)
.DataLabel.Text = "='" & aRng.Parent.Name & "'!" _
& aRng.Cells(i).Address(ReferenceStyle:=xlR1C1)
End With
Next i
End With
End With
However this only works well for a 1 Series Graph (i.e. see sheet Data in attached excel workbook), if you have 2 Series or more graph and have empty Y values (and so for this specific XY there is no data point) this code fails.. See sheet Data_2 or more Series..
I am wondering how to work around that but seems rather impossible from where I am..
P.S I can provide the whole routine as obviously I am seeting ranges and X&Y values dynamically.. but code above is just an example.