title: Calculate Excel formulas in VBA but display only the value in Excel
body: <p>This is my first post and I can't find an exact answer anywhere. I have an Excel spreadsheet that is becoming too large to operate because I have long formulas in millions of cells. I need to know how to calculate the formula using VBA but have only the value appear in Excel.  An example is I want to multiply column B by column C:</p>
  
  <p>I have tried this code:</p>
  
  <pre><code>Range("D3:D6").Formula = Evaluate("=B3*C3")
  </code></pre>
  
  <p>It correctly calculates for the first cell, but for the other cells it still tries to calculate B3*C3 as a fixed reference rather than as a dynamic references that changes as the cell position changes.</p>
  
  <p>How could I fix this? Thankyou.</p>
  
  <p>Edit:</p>
  
  <p>My actual spreadsheet looks like this:</p>
  
  <p><img src="https://i.stack.imgur.com/0pYBz.png" alt="Excel Screenshot 2"></p>
  
  <p>This formula needs to be applied down 17520 rows and across 300 columns, but I only want the values to appear in Excel.  The purpose of this is to reduce file size and reduce calculation time.</p>
  
  <pre><code>=IF(-SUMIF(AIG$4:AIG4,"&lt;0")&gt;0.9*SUMIF(AIG$4:AIG4,"&gt;0"),0,IF(-SUMIF(AIG$4:AIG4,"&lt;0")-IF(WQ5&lt;'Battery Specs'!$B$13,-'Battery Specs'!$B$15,IF(WQ5=ROUNDDOWN('Battery Specs'!$B$13,0)+1,-('Battery Specs'!$B$13-ROUNDDOWN('Battery Specs'!$B$13,0))*'Battery Specs'!$B$15,0))&gt;0.9*SUMIF(AIG$4:AIG4,"&gt;0"),-(0.9*SUMIF(AIG$4:AIG4,"&gt;0")+SUMIF(AIG$4:AIG4,"&lt;0")),IF(WQ5&lt;'Battery Specs'!$B$13,-'Battery Specs'!$B$15,IF(WQ5=ROUNDDOWN('Battery Specs'!$B$13,0)+1,-('Battery Specs'!$B$13-ROUNDDOWN('Battery Specs'!$B$13,0))*'Battery Specs'!$B$15,IF((0.9*SUMIF(AIG$4:AIG4,"&gt;0")+SUMIF(AIG$4:AIG4,"&lt;0"))&gt;'Battery Specs'!$B$10,0,IF(((0.9*SUMIF(AIG$4:AIG4,"&gt;0")+SUMIF(AIG$4:AIG4,"&lt;0"))+$AIF5*0.9)&gt;'Battery Specs'!$B$10,'Battery Specs'!$B$10-(0.9*SUMIF(AIG$4:AIG4,"&gt;0")+SUMIF(AIG$4:AIG4,"&lt;0")),$AIF5))))))
  </code></pre>