Summing growing range

[rmoon]

I have been trying to code a change in a formula to sum the number of cells that match up to the current month. (If the current month is May, I want to sum Jan:May and next time I run in June, I want Jan:Jun) The current month formatted as =Month(Date of Worksheet) is found in my Table of Contents worksheet.


Here is the code I have for the worksheet that includes the sum function.

Range("O5").Select
Dim i As Integer
i = Worksheets("Table of Contents").Range("H5")
FormulaR1C1 = "=+SUM(RC[-13]:RC[-13+i])"
With Range("O5")
.Resize(5, 1).FillDown
 End With
 Range("O15").Select

Thanks!

[SamT]

assuming that Jan is in Column B,
Add this line to see the problem

i = Worksheets("Table of Contents").Range("H5")
MsgBox -13 + i

To cheat and see the answer, drag the mouse over the next line of this post.
i = Worksheets("Table of Contents").Range("H5") - 1

[rmoon]

Right, but when I run the code it isn't updating the formula to contain the next month. Right now the cell includes the formula Sum(B5:E5), but I want it to run and now become Sum(B5:F5). Any suggestions how to make it work?

    Range("O5").Select
 Dim i As Integer
i = Worksheets("Table of Contents").Range("H5") - 1
FormulaR1C1 = "=+SUM(R[1]C[-13]:R[1]C[-13+ (i))"
With Range("O5")
.Resize(5, 1).FillDown
 End With
 Range("O15").Select

[SamT]

This is the only way I could make it work

i = 6 
With Range("O5")
   .FormulaR1C1 = "=SUM(RC2:RC" & i & ")"
   .Resize(5, 1).FillDown
End With

[rmoon]

Thank you! I messed with this configuration and got it to work