# Basic Subtract constant loop

#### eawachte

##### New Member
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
eawachte,

Sample raw data in worksheet Sheet1:

Excel 2007
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
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
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")
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.

Replies
5
Views
243
Replies
2
Views
196
Replies
8
Views
272
Replies
3
Views
325
Replies
2
Views
249

1,203,619
Messages
6,056,325
Members
444,860
Latest member
Daz511

### 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.

### Which adblocker are you using?

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

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