title: #NAME error when setting cell formula through VBA.
body: <p>I am writing a macro that requires me to get the average of the values in a column with an unknown number of rows. I use this to get the number of the last row:</p>
  
  <pre><code>Dim lastRow As Long
  lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  </code></pre>
  
  <p>That works. What doesn't work is when I try to use it here:</p>
  
  <pre><code>Range("B2").Select
  ActiveCell.FormulaR1C1 = "=AVERAGE('table1'!AM2:AM" &amp; lastRow &amp; ")"
  </code></pre>
  
  <p>Doing that, I get a result of <code>#NAME?</code>. How can I fix this?</p>