Basic Subtract constant loop

eawachte

New Member
Joined
Jan 14, 2011
Messages
29
I have a given range AD11:AD51. I make the first value in that range a constant variable => LFSTART

I now am trying to run a loop through the range and subtract LFSTART from each value in the range and post the result in the column 2 over AF11:AF51

I have moved lots of data and created different userforms, but am stuck trying to find out how to do this kind of data analysis

Thanks for any help

Gene
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
eawachte,

Sample raw data in worksheet Sheet1:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>AD</th><th>AE</th><th>AF</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1.11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;;">36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;;">39</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>AD</th><th>AE</th><th>AF</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1.11</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.11</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">-0.11</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.89</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">1.89</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">2.89</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">3.89</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">4.89</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">5.89</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">6.89</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">7.89</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">8.89</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">9.89</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">10.89</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">11.89</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">12.89</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">13.89</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">14.89</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">15.89</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;">16.89</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;">17.89</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;">18.89</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;">19.89</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;">20.89</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;">21.89</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;">22.89</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;">23.89</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;">24.89</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;">25.89</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;">26.89</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;">27.89</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;">28.89</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;">29.89</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;">30.89</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;">31.89</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td><td style="text-align: right;;">32.89</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;">33.89</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;;">36</td><td style="text-align: right;;"></td><td style="text-align: right;;">34.89</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;">35.89</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;">36.89</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="text-align: right;;">39</td><td style="text-align: right;;"></td><td style="text-align: right;;">37.89</td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;">38.89</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub SubtractFromArray()
' hiker95, 04/08/2013
' http://www.mrexcel.com/forum/excel-questions/695984-basic-subtract-constant-loop.html
Dim a As Variant, i As Long
With Sheets("Sheet1")
  a = .Range("AD11:AD51").Value
  For i = 2 To UBound(a, 1)
    a(i, 1) = a(i, 1) - a(1, 1)
  Next i
  .Range("AF11").Resize(UBound(a, 1)) = a
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SubtractFromArray macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top