title: SUM formula VBA
body: <p>I am trying to calculate the sum of changing cell range in vba. Unfortunately the cell values are variables. I can't seem to get the following formula to work. </p>
  
  <pre><code>Private Sub calcOverheadRate(startCell As Integer, endCell As Integer)
       Total = endCell + 1
       Range("D" &amp; Total).Formula = "=SUM("D" &amp; startCell &amp; ":" &amp; "D" &amp; endCell)"
  End Sub
  </code></pre>
  
  <p>I get compile error: "Expected: end of statement</p>
  
  <p>To solve this problem I changed the function to, </p>
  
  <pre><code>Private Sub calcOverheadRate(startCell As Integer, endCell As Integer)
  
      Dim start As String
      Dim endC As String
  
      start = "D" &amp; CStr(startCell)
      endC = "D" &amp; CStr(endCell)
  
      Total = endCell + 1
  
      Range("D" &amp; Total).Formula = "=SUM(start:endC)"
  
  End Sub
  </code></pre>
  
  <p>The function compiles fine, when I run it, the value in the cell is "#NAME" where it references <code>SUM(start:endC)</code> not <code>SUM(D5:D23)</code>....</p>
  
  <p>Any thoughts on how to solve this would be appreciated.  </p>