Create VBA Loop to Sum 5 cells, Enter Value in 5th Cell
I unfortunately know almost nothing about VBA, so I'm really depending on you guys for help.
I'm trying to write a code that, in a new column(AN), a) sums 5 cells in another column(AM), b)re-starts the loop at the end of 5 cells, c)uses the 5th cell as the 1st value in the next loop and d)enters the sum in the 5th cell of column (AN). To be clear, I'd like the sum of AM4:AM8 to be populated in AN8, and so on, with the next sum to pull AM8 as its first value. There are also FALSE statements in column (AM) A standard sum offset will populate straight down instead of offsetting again, so I need a vba code.
I've attached the workbook. The values I want summed are in AM, and I'd like the sums to be in column AN.
I don't know if this is possible or hard, but I would be so unbelievable appreciative of help!
| Attachment | Size |
|---|---|
| Book1.xlsx | 439.33 KB |
The code you need
Here you have the code you need, I hope it helps you:
Sub sum5cells()
Dim i, j As Integer
For i = 0 To 13
ActiveSheet.Range("AN8").Offset(2 * i, 0).FormulaR1C1
= "=SUM(R[-4]C[-1]:R[0]C[-1])"
i = i + 1
Next i
End Sub
If you have any doubts just send them! :)
Cátia Santos
VBA code
Cátia your code appears to be just fine. It is indeed adding the correct cells as per your description. So what needs to be done here?