CODEWITHSUNDEEP
vbaexcel
SMORF
SMORF

Reputation: 499

Set sum range when range varies

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?

Upvotes: 1

Views: 192

Answers (1)

user1016274
user1016274

Reputation: 4209

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

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

Upvotes: 4

Related Questions