title: Excel 2013 proper syntax for dynamic data range in VBA loop
body: <p>Nanashi's reply helped me to modify the code below; the chart now responds properly.  Would like to adjust further such that strfieldrange will offset one column farther to the right each time the loop steps to the next series (e.g., $Z$4:$Z$159 should become $AA$4:$AA$159 in the second step of the loop).  I'm learning so please excuse the rough edges.</p>
  
  <pre><code>Sub stacklabels2()
  
  finalrow = Worksheets("Stacking RR").Range("Z" &amp; Rows.Count).End(xlUp).Row 'trying to derive the proper range dynamically
  ' sensing that I need another variable here that will allow me to cause strfieldrange to step with the loop
  strfieldrange = "=" &amp; "'Stacking RR'!" &amp; Range("$Z$4:$Z$159").Address
  
  Sheets("Stacking").Select
  ActiveSheet.ChartObjects("StackingPlan").Activate
  
  ActiveChart.SetElement (msoElementDataLabelNone)
  ActiveChart.ApplyDataLabels
  
  ActiveSheet.ChartObjects("StackingPlan").Activate
  For Each Ser In ActiveChart.SeriesCollection
      Ser.DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, strfieldrange, 0
      Ser.DataLabels.ShowRange = True
      Ser.DataLabels.ShowValue = False
  Next Ser
  End Sub
  </code></pre>
  
  <p>Any ideas?  Thanks.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I think I solved it.  Substituting the following code into the larger macro from above works:</p>
  
  <pre><code>keycolumn = Range("Z4").Column
  
  For Each Ser In ActiveChart.SeriesCollection
      n = Val(Ser.Name)
      Ser.DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, CStr("='Stacking RR'!" &amp; Range(Cells(4, keycolumn + n), Cells(LastRow, keycolumn + n)).Address), 0
      Ser.DataLabels.ShowRange = True
      Ser.DataLabels.ShowValue = False
  Next Ser
  </code></pre>
  
  <p>Awesome.  Thanks to <a href="https://stackoverflow.com/users/2548721/nanashi">@nanashi</a> for all of his help.</p>