title: How to use variable inside variable in cell .address
body: <p>I have several column of discontinuous data, each column contain some continues reading which needs to be summed up. So summation at different ranges is required. Summation will only be made if x is greater than 1 and its value define the size of range (number of row to be selected above the active cell) .  However "rng1 = .. address" resulted an application-defined or object-defined error. My code is not so neat but I'm sure all variable hold desired value, please help..</p>
  
  <pre><code>Sub Dynamic_rowsRangeNumbers()
  Dim rng1 As String, rng2 As String
  Dim nrow As Integer
  Dim x, ncol As Integer
  
  ThisWorkbook.Worksheets("Sheet5").Activate
  For ncol = 2 To 4
    For nrow = 3 To 28
    x = Cells(nrow, 216 + ncol).Value 
    rng1 = Cells(nrow - x + 1, ncol).Address *** run-time error 1004
    rng2 = ActiveCell.Address
  
    'If reference cell has value FZ&gt;=1 Then
     If (Cells(nrow, 216 + ncol).Value &gt;= 1) Then
     Cells(nrow, 254 + ncol).Formula = "=sum(" &amp; (rng1) &amp; ":" &amp; (rng2) &amp; ")"
     End If
    Next nrow
  Next ncol
  End Sub
  </code></pre>