Code for autofilling a formula to a predefined range

[JohanGilliaert]

Hi,

total beginner here... I have a set of data in a range from Q5 to AB20, each column is a month, in each column there can be numbers (depending on certain formulas) and underneath every month I need to make the sum of all the days for that month. I succeeded in doing that for column Q (it automatically finds the first empty cell and makes the sum allright), but now I want to autofill this formula to the right until column AB so I get the total for each month. Which code do I need to autofill the formula from Q to AB?

I used this to find the first free cell in Q and make the sum:

' Somtest Macro
'
LastRowNumber = Range("Q" & Rows.Count).End(xlUp).Row
MoveUp = LastRowNumber - 5
Cells(LastRowNumber + 1, "Q").FormulaR1C1 = "=SUBTOTAL(9,R[-" & MoveUp & "]C:R[-1]C)"
'
End Sub

One thing weird about it, it works the first time, but when I run it again it doesn't add the last number to the next sum, f.e.:

1
2
3
4

I run it and get

1
2
3
4
10

Perfect, but now when I run it again I get

1
2
3
4
10
10

When I would be expecting 20...?

Hope it's clear....

Thx!

[wallyeye]

The subtotal function will ignore previous subtotals, you could use a sum.

[JohanGilliaert]

So simply replace SUBTOTAL by SUM? I just copied the formula off the forum somewhere and it seemed to work just fine except for that little problem...