Set sum range when range varies

1
votes

I need to use a sum formula in my VBA script that always starts at cell B10 but, will finish at a different cell (always in column B) depending on results of the previous macro's.

The first cell for sum range will always be cell B10 ... the end of the sum range will always be 3 rows above the cell that formula is populating.

I tried the following but, I get

'Compile error: Argument not optional' error.

ActiveCell.Formula = "=SUM(B10:" & ActiveCell.Offset(-3, 0).Range & "))"

How can I use a varying cell reference in my formula?

1
vbaexcel
use ActiveCell.Offset(-3, 0).Address not .range... Just a side note that Activecell is not a "good code practise" for programming in Excel - 99moorem

1 Answers

4
votes

As you are only looking for the Row value you can better use that property directly: 

ActiveCell.Formula = "=SUM(B10:B" & (ActiveCell.Row - 3) & ")"