Link a series name to a cell using Excel 2010 VBA

I am trying to create a chart in Excel 2010 using VBA. Adding the X and Y values works fine, but for the series name it applies the text currently in the cell instead of a link to the cell. In this case the series label may change depending on user input, so I need to link to the cell instead of assign a fixed string value. Is there any way to do this? It is simple enough doing it manually in the worksheet, but I can't seem to figure out how to do it in VBA. My basic code is shown below.

With srsNew
.Name = ActiveSheet.Cells(N, ColNum)
.Values = ActiveSheet.Range(Cells(N, ColNum + 1), Cells(N,ColNum + 2))
.XValues = ActiveSheet.Range(RFRAngeAddr)
...

Thanks for any advice

Hi there,

I recorded linking the title to a cell and it came back with the following. Does this help?


So basically the R1C1 is A1. B1 would be R1C2.

Thanks. This solved the problem.