title: VBA - How to keep a Dynamic FormulaR1C1 that evaluates?
body: <p>I've been at this for days now. I have tried everything from stringing the formulae to using FormulaR1C1 and evaluate. </p>
  
  <p>I have something close to 35000+rows of data and want to limit the time the calculation takes and also the time taken to filter through colours etc.(conditional formatting)</p>
  
  <p>I was hoping for a code that would allow me to "update (button)" and just populate the result and not the formulae across all rows.</p>
  
  <p>Please see my excel formulae:</p>
  
  <pre><code>=IF(D2&gt;0,1,0)+IF(E2&gt;0,1,0)+IF(F2&gt;0,1,0)+IF(G2&gt;0,1,0)
  =IF(SUMIF(B:B,B2,D:D)&gt;0,1,0)+IF(SUMIF(B:B,B2,E:E)&gt;0,1,0)+IF(SUMIF(B:B,B2,F:F)&gt;0,1,0)+IF(SUMIF(B:B,B2,G:G)&gt;0,1,0)+IF(SUMIF(B:B,B2,H:H)&gt;0,1,0)
  =IF(B2=B1,C2-C1,0)
  </code></pre>
  
  <p>I have tried the following -</p>
  
  <pre><code>Sub COLUMNS2stringg()
  
   Dim strFormulas(1 To 3) As Variant
  
  With ThisWorkbook.Sheets("working")
     strFormulas(1) = "=IF(RC[-5]&gt;0,1,0)+IF(RC[-4]&gt;0,1,0)+IF(RC[-3]&gt;0,1,0)+IF(RC[-2]&gt;0,1,0)"
      strFormulas(2) = "=IF(SUMIF(C[-8],RC[-8],C[-6])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-5])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-4])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-3])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-2])&gt;0,1,0)"
      strFormulas(3) = "=IF(RC[-9]=R[-1]C[-9],RC[-8]-R[-1]C[-8],0)"
      .Range("i3:k3").Formula = strFormulas
      .Range("i3:k35000").FillDown
  End With
  
  End Sub
  </code></pre>
  
  <p>I have also tried the following -</p>
  
  <pre><code>Sub COLUMNS2()
  
  Range("I3").Select
  ActiveCell.FormulaR1C1 = "=IF(RC[-5]&gt;0,1,0)+IF(RC[-4]&gt;0,1,0)+IF(RC[-3]&gt;0,1,0)+IF(RC[-2]&gt;0,1,0)"
  Range("i3:i35000" &amp; lrow).FillDown
  Range("J3").Select
  ActiveCell.FormulaR1C1 = "=IF(SUMIF(C[-8],RC[-8],C[-6])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-5])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-4])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-3])&gt;0,1,0)+IF(SUMIF(C[-8],RC[-8],C[-2])&gt;0,1,0)"
  Range("j3:j35000" &amp; lrow).FillDown
  Range("K3").Select
  ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],RC[-8]-R[-1]C[-8],0)"
  Range("k3:k35000" &amp; lrow).FillDown
  
  End Sub
  </code></pre>
  
  <p>I have also tried - </p>
  
  <pre><code> Sub overit2()
  
  Dim strFormulas(1 To 3) As Variant
  With ThisWorkbook.Sheets("working")
      strFormulas(1) = "=IF("$D3"&gt;0,1,0)+IF("$E3"&gt;0,1,0)+IF($F3&gt;0,1,0)+IF($G3&gt;0,1,0)"
      strFormulas(2) = "=IF(SUMIF(B:B,$B3,D:D)&gt;0,1,0)+IF(SUMIF(B:B,$B3,E:E)&gt;0,1,0)+IF(SUMIF(B:B,$B3,F:F)&gt;0,1,0)+IF(SUMIF(B:B,$B3,G:G)&gt;0,1,0)+IF(SUMIF(B:B,$B3,H:H)&gt;0,1,0)"
      strFormulas(3) = "=IF($B3=$B2,$C3-$C2,0)"
      .Range("j3").Formula = strFormulas(1)
      .Range("j3:j35000" &amp; lrow).FillDown
      .Range("k3").Formula = strFormulas(2)
      .Range("k3:k35000" &amp; lrow).FillDown
      .Range("l3").Formula = strFormulas(3)
      .Range("l3:l35000" &amp; lrow).FillDown
   End With
  
   End Sub
  </code></pre>
  
  <p>Where am I going wrong? I have tried a magnitude of variations :( please help </p>