How to calculate sum of a moving range and average it

Hello all,

A bit long title :D

I am trying to calculate a moving range based on a defined period

Example:

I have 12 data: 1,2,3,4,5,6,7,8,9,10,11,12
And the period is 4 (written in a cell), note that the period can be different for a different set of data

So the calculation that needs to be performed is: (1+2+3+4)+(2+3+4+5)+,...,+(9+10+11+12) divided by (12-4-1)

How can I write a syntax in VBA to perform such functin in one cell?

My failed test syntax is as follows:

Sample data is from D8 to D19
cell containing period value is E7
And the result should be in cell E8

Sub test()
Dim lr0 As Long
lr0 = Worksheets("sheet1").Range("D8").End(xlDown).Row


For i = 8 To lr0 - 8 + 1 - (Worksheets("sheet1").Range("E7") - 1) Step 1
For j = 8 + Worksheets("sheet1").Range("E7") - 1 To lr0 Step 1
Worksheets("sheet1").Cells(8, 5) = Application.Sum(Worksheets("sheet1").Range("D" & i&), Worksheets("sheet1") _
.Range("D" & j&))
Next j
Next i


End Sub

Can anyone assist me on this?
Thanks in advance!

PL try this code
code:

Sub test()
Dim lr0 As Long, T_sum As Long, period As Long, i As Long
lr0 = Worksheets("sheet1").Range("D8").End(xlDown).Row

period = Worksheets("sheet1").Range("E7")

For i = 8 To lr0 - period + 1
T_sum = T_sum + WorksheetFunction.Sum(Range(Cells(i, "D"), Cells(i + period - 1, "D")))
Next i

Worksheets("sheet1").Range("E8") = T_sum / (Cells(lr0, "D") - period - 1)

End Sub

Originally Posted by kvsrinivasamurthy

PL try this code
code:

It gives me zero on the result...

The T_sum might gives zero on the result

I addded T_sum = 0 at the beginning

It gives me number.. Now I am gonna check the result..

Sub test()
Dim lr0 As Long, T_sum As Long, period As Long, i As Long
lr0 = Worksheets("sheet1").Range("D8").End(xlDown).Row

period = Worksheets("sheet1").Range("E7")
T_sum = 0 --> I add this

For i = 8 To lr0 - period + 1
T_sum = T_sum + WorksheetFunction.Sum(Range(Cells(i, "D"), Cells(i + period - 1, "D")))
Next i

Worksheets("sheet1").Range("E8") = T_sum / (Cells(lr0, "D") - period - 1)

End Sub

Well it is not about giving T_sum an initial value

Sub test()

Dim lr0 As Long, T_sum As Long, period As Long, i As Long
lr0 = Worksheets("sheet1").Range("D8").End(xlDown).Row

period = Worksheets("sheet1").Range("E7")
T_sum = 0

For i = 8 To lr0 - period + 1
T_sum = T_sum + Application.Sum(Range(Worksheets("sheet1").Cells(i, 4), Worksheets("sheet1").Cells(i + period - 1, 4)))
Next i

Worksheets("sheet1").Range("E8") = T_sum / (Worksheets("sheet1").Cells(lr0, 4) - period + 1)
Worksheets("sheet1").Range("E9") = lr0
Worksheets("sheet1").Range("E10") = period
Worksheets("sheet1").Range("E11") = T_sum

End Sub

The range syntax must be added worksheets("sheet1") bla bla..

Thanks! It works like a charm!