Looping through a range of rows using a string

1
votes

I am trying to create a macro that will enter ranges within a chart's data labels via the "Label Contains: Value From Cells" option. Each label corresponds to a row within a sheet such as =Breakdown!$B$27:$K$27. I recorded a macro to find the language for this action and it came back as:

ActiveChart.SeriesCollection(i).DataLabels.Format.TextFrame2.TextRange. _
    InsertChartField msoChartFieldRange, "=Breakdown!$B$27:$K$27", 0

I can loop through the SeriesCollection by "Dim i As Integer". Is there any way I can set =Breakdown!$B$27:$K$27 as a string or something with variables for the cell numbers so that I can cycle through the rows?

1
vbaexcel

1 Answers

0
votes

Using string concatenate operator & and CStr function which converts an expression into a string, it'll work out well.

"=Breakdown!$B$" & CStr(i) & ":$K$" & CStr(i)