PDA

View Full Version : [SOLVED] Summing growing range



rmoon
06-22-2015, 06:21 AM
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
06-22-2015, 07:42 AM
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
06-22-2015, 12:13 PM
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
06-22-2015, 02:15 PM
:dunno 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
06-23-2015, 06:55 AM
Thank you! I messed with this configuration and got it to work :)