VBA for extending formula

[mlarson]

I would like to extend the formulas below for about 100,000 rows but with only the output showing in a cell (and the formula not copying into each cell). What is the code to do this? Thanks a ton in advance if you can help.

<table width="552" border="0" cellpadding="0" cellspacing="0"><col style="width: 30pt;" width="40"> <col style="width: 96pt;" width="128" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 30pt;" width="40" height="20">A</td> <td style="width: 96pt;" width="128">B</td> <td style="width: 96pt;" width="128">C</td> <td style="width: 96pt;" width="128">D</td> <td style="width: 96pt;" width="128">E</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">2</td> <td class="xl63">162.6</td> <td class="xl63">161.57</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">3</td> <td class="xl63">164.49</td> <td class="xl63">163.3</td> <td class="xl63">=B3-C2</td> <td>=D3*C3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">4</td> <td class="xl63">164.75</td> <td class="xl63">163.02</td> <td class="xl63">=B4-C3</td> <td>=D4*C4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">5</td> <td class="xl63">163.92</td> <td class="xl63">157.47</td> <td class="xl63">=B5-C4</td> <td>=D5*C5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">6</td> <td class="xl63">157.26</td> <td class="xl63">158.99</td> <td class="xl63">=B6-C5</td> <td>=D6*C6</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">7</td> <td class="xl63">158.51</td> <td class="xl63">156.98</td> <td class="xl63">=B7-C6</td> <td>=D7*C7</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">8</td> <td class="xl63">156.2</td> <td class="xl63">154.48</td> <td class="xl63">=B8-C7</td> <td>=D8*C8</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">9</td> <td class="xl63">154.48</td> <td class="xl63">156.67</td> <td class="xl63">=B9-C8</td> <td>=D9*C9</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">10</td> <td class="xl63">156.29</td> <td class="xl63">158.01</td> <td class="xl63">=B10-C9</td> <td>=D10*C10</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">11</td> <td class="xl63">158.94</td> <td class="xl63">158.71</td> <td class="xl63">=B11-C10</td> <td>=D11*C11</td> </tr> </tbody></table>
OUTPUT
<table width="552" border="0" cellpadding="0" cellspacing="0"><col style="width: 30pt;" width="40"> <col style="width: 96pt;" width="128" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 30pt;" width="40" height="20">A</td> <td style="width: 96pt;" width="128">B</td> <td style="width: 96pt;" width="128">C</td> <td style="width: 96pt;" width="128">D</td> <td style="width: 96pt;" width="128">E</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">2</td> <td class="xl65">162.6</td> <td class="xl65">161.57</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">3</td> <td class="xl65">164.49</td> <td class="xl65">163.3</td> <td class="xl66">2.92</td> <td class="xl66">476.836</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">4</td> <td class="xl65">164.75</td> <td class="xl65">163.02</td> <td class="xl66">1.45</td> <td class="xl66">236.739</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">5</td> <td class="xl65">163.92</td> <td class="xl65">157.47</td> <td class="xl66">0.9</td> <td class="xl66">141.723</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">6</td> <td class="xl65">157.26</td> <td class="xl65">158.99</td> <td class="xl66">-0.21</td> <td class="xl66">-33.38</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">7</td> <td class="xl65">158.51</td> <td class="xl65">156.98</td> <td class="xl66">-0.48</td> <td class="xl66">-75.35</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">8</td> <td class="xl65">156.2</td> <td class="xl65">154.48</td> <td class="xl66">-0.78</td> <td class="xl66">-120.49</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">9</td> <td class="xl65">154.48</td> <td class="xl65">156.67</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">10</td> <td class="xl65">156.29</td> <td class="xl65">158.01</td> <td class="xl66">-0.38</td> <td class="xl66">-60.04</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">11</td> <td class="xl65">158.94</td> <td class="xl65">158.71</td> <td class="xl66">0.93</td> <td class="xl66">147.6</td> </tr> </tbody></table>

 

[Joe4]

Copy your formula all the way down for all rows (it should adjust the range refererences automatically).

Then if you want the formulas replaced by their actual value, highlight those columns and Copy and the right click and select Paste Special -> Values and click Enter.

 

[mlarson]

Joe, do you know the macro that will do that automatically? Some of my spreadsheets have hundreds of thousands of rows of data and I don't want to copy the formulas down each row. Thanks.

 

[Joe4]

Sure.

Sub MyMacro1()
 
    Dim myLastRow As Long
    
'   Find last row
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Populate columns D and E with formulas
    Range("D3:D" & myLastRow).FormulaR1C1 = "=RC[-2]-R[-1]C[-1]"
    Range("E3:E" & myLastRow).FormulaR1C1 = "=RC[-1]*RC[-2]"
    
'   Change formulas to values
    Range("D3:E" & myLastRow).Copy
    Range("D3:E" & myLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
End Sub

BTW, much of this code can be obtained by using the Macro Recorder (as you record yourself performing the steps manually). That often gives you a good starting point to work from.

 

[mlarson]

Joe, thanks for this info!