title: Copy a reference formula down to stay in line with updating data
body: <p>I have a macro and one of the columns it produces is a column of dates. I wish to use this excel formula</p>
  
  <pre><code>=IF(COUNTIF(Sheet2!A9:A102,K2)&gt;0, K2,J1)
  </code></pre>
  
  <p>But have it copy down so it always goes as far as the column of dates do its left. Basically when a new date gets added, this formula will copy down another cell. I tried using this:</p>
  
  <pre><code>Range("J2:J" &amp; LastRow).FormulaR1C1 = "=IF(COUNTIF(Sheet2!R[7]C[-9]:R[100]C[-9],RC[1])&gt;0, RC[1],R[-1]C)"
  </code></pre>
  
  <p>But get "method 'range' of object '_global' failed.</p>