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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

hiker95

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

Sample raw data in worksheet Sheet1:


Excel 2007
ADAEAF
111.11
121
132
143
154
165
176
187
198
209
2110
2211
2312
2413
2514
2615
2716
2817
2918
3019
3120
3221
3322
3423
3524
3625
3726
3827
3928
4029
4130
4231
4332
4433
4534
4635
4736
4837
4938
5039
5140
52
Sheet1


After the macro:


Excel 2007
ADAEAF
111.111.11
121-0.11
1320.89
1431.89
1542.89
1653.89
1764.89
1875.89
1986.89
2097.89
21108.89
22119.89
231210.89
241311.89
251412.89
261513.89
271614.89
281715.89
291816.89
301917.89
312018.89
322119.89
332220.89
342321.89
352422.89
362523.89
372624.89
382725.89
392826.89
402927.89
413028.89
423129.89
433230.89
443331.89
453432.89
463533.89
473634.89
483735.89
493836.89
503937.89
514038.89
52
Sheet1


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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,092
Messages
5,835,344
Members
430,351
Latest member
ddalton

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