Sum range of cells in active cell

[BYURAZORBACK]

I have a report that comes out daily and has a different number of rows each day. I need to have the vba code that will calculate the sum for a range of cells. The first cell should always be Q4. I have used auto fill to copy a formula down using Range("Q4:Q" & lastRow), but I'm not sure how to use that properly here. Currently I'm using: ActiveCell.FormulaR1C1 = "=SUM(R[-16]C[0]:R[-1]C[0])"
Only because 16 rows up worked the first day I created this.

 

[JoeMo]

I have a report that comes out daily and has a different number of rows each day. I need to have the vba code that will calculate the sum for a range of cells. The first cell should always be Q4. I have used auto fill to copy a formula down using Range("Q4:Q" & lastRow), but I'm not sure how to use that properly here. Currently I'm using: ActiveCell.FormulaR1C1 = "=SUM(R[-16]C[0]:R[-1]C[0])"
Only because 16 rows up worked the first day I created this.

Try:

lastRow = Range("Q" & Rows.Count).End(xlUp).Row
ActiveCell.Formula = "=SUM(" & Range("Q4:Q" & lastRow).Address(1, 1) & ")"