Dynamic Chart Series Labels

I have some Visual Basic Code that creates a chart for each row. It sets the series values using this code:

.SeriesCollection(1).Va开发者_JAVA技巧lues = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8"

What I am struggling with is how do I set the series labels? The series labels will always be the 1st row and be in the corresponding column. I know this is much simplier than the code above, but I am stumped.

Any help is appreciated.

---

Use the Ws.Range("C1:H1").Value in the .SeriesCollection(1).ApplyDataLabels Method.

---

I'm guessing from your command for the Y Values, you want this:

.SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2"

which is the same row as the Y values, the column before the Y values start.

---

My first answer interpreted the question as asking for the series name. If you want to use a range for the data labels, it's a bit trickier.

If you have Excel 2013, you can directly assign a range:

With .SeriesCollection(1)
  .ApplyDataLabels
  With .DataLabels
    .Format.TextFrame2.TextRange.InsertChartField _
        msoChartFieldRange, "='" & Ws.Name & "'!R1C3:R1C8", 0
    .ShowRange = True
    .ShowValue = False
  End With
End With

If you have an earlier version of Excel, you have to step through the labels.

This assigns the static text of the cells to the labels:

Dim iPt As Long
Dim rLabels As Range

Set rLabels = Ws.Range(Ws.Cells(1, 3), Ws.Cells(1, 8))

With .SeriesCollection(1)
  .ApplyDataLabels
  For iPt = 1 To .Points.Count
    .Points(iPt).DataLabel.Text = rLabels.Cells(iPt).Text
  Next
End With

This links each label to the corresponding cell, so the label reflects any changes in the cells:

Dim iPt As Long
Dim rLabels As Range

Set rLabels = Ws.Range(Ws.Cells(1, 3), Ws.Cells(1, 8))

With .SeriesCollection(1)
  .ApplyDataLabels
  For iPt = 1 To .Points.Count
    .Points(iPt).DataLabel.Text = "=" & rLabels.Cells(iPt).Address(, , , True)
  Next
End With