Basic Subtract constant loop

[eawachte]

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

 

[hiker95]

eawachte,

Sample raw data in worksheet Sheet1:

Excel 2007
	AD	AE	AF
11	1.11
12	1
13	2
14	3
15	4
16	5
17	6
18	7
19	8
20	9
21	10
22	11
23	12
24	13
25	14
26	15
27	16
28	17
29	18
30	19
31	20
32	21
33	22
34	23
35	24
36	25
37	26
38	27
39	28
40	29
41	30
42	31
43	32
44	33
45	34
46	35
47	36
48	37
49	38
50	39
51	40
52
Sheet1

After the macro:

Excel 2007
	AD	AE	AF
11	1.11		1.11
12	1		-0.11
13	2		0.89
14	3		1.89
15	4		2.89
16	5		3.89
17	6		4.89
18	7		5.89
19	8		6.89
20	9		7.89
21	10		8.89
22	11		9.89
23	12		10.89
24	13		11.89
25	14		12.89
26	15		13.89
27	16		14.89
28	17		15.89
29	18		16.89
30	19		17.89
31	20		18.89
32	21		19.89
33	22		20.89
34	23		21.89
35	24		22.89
36	25		23.89
37	26		24.89
38	27		25.89
39	28		26.89
40	29		27.89
41	30		28.89
42	31		29.89
43	32		30.89
44	33		31.89
45	34		32.89
46	35		33.89
47	36		34.89
48	37		35.89
49	38		36.89
50	39		37.89
51	40		38.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.

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.