Using offset to copy formula down a column

[Awsib]

Hi

I am trying to apply a formula where I need to refer to a few cells to the left of the formula cell and a few cells to the up and apply the formula down the column for quite a few rows.

I am trying to achieve the result by using offset function, but I am not getting the desired result.

I would be really grateful of any guidance.

Sub ResultAchievedIsNotAsRequired()

Dim variableA As Long

variableA = Worksheets("DATA").Range("D7").Value

Dim LeftRow As Long

LeftRow = ActiveCell.Offset(0, -1).Value

Dim Upperrow As Long

Upperrow = ActiveCell.Offset(-1, 0).Value

Dim q As Long

Worksheet("MyCalculation").Range("B2").Select

For q = 5 To 10

Selection.Value = (ActiveCell.Offset(0, -1).Value * (2 / (VariableA + 1)) + ActiveCell.Offset(-1, 0).Value * (1 - (2 / (VariableA + 1))))

ActiveCell.Offset(1, 0).Activate

Next

End Sub

A somewhat similar formula may help in understanding my query better:-

Book1
	E	F	G	H	I
9	445.64	3,198,600	445.64
10	443.05	2,860,400	443.05
11	452.21	2,590,300	452.21
12	453.73	2,389,800	453.73
13	451.14	2,342,000	451.14
14	450.36	2,110,900	450.36		442.82
15	457.10	2,543,100	457.1		444.86
16	456.61	1,742,000	456.61		446.54
17	453.94	1,712,500	453.94		447.60
18	458.58	2,341,400	458.58		449.17
19	462.28	1,995,200	462.28		451.04
20	460.00	1,675,100	460		452.32
21	444.89	2,620,200	444.89		451.26
22	445.28	2,351,100	445.28		450.40
23	443.97	2,255,000	443.97		449.48
24	460.41	3,998,900	460.41		451.05
25	465.24	3,560,500	465.24		453.07
26	468.73	2,453,400	468.73		455.31
27	471.37	2,341,000	471.37		457.60
MACD

Cell Formulas
Range	Formula
I14	I14	=SUM(E2:E14)/13
I15:I27	I15	=(E15*J$3)+(I14*(1-J$3))