CODEWITHSUNDEEP
vbaexcel
tothakos999
tothakos999

Reputation: 41

How to create formula to sum with dynamic ranges? I just can't make the right syntax

So what I would like to do is add a sum formula to a cell, so it can be edited later(normally, not through vba). It sums up some cells, but the amount of cells is not always the same. Sometimes it's 4 cells, sometimes it's 10. So I'm trying to have:

lastrow = Sheets(7).Cells(Sheets(7).Rows.Count, "A").End(xlUp).Row

then using it:

Sheets(7).Range("B" & lastrow + 1).Formula = "=SUM(b2:b&"lastrow")"

My problem is the syntax actually, I can't seem to make it right. How to add lastrow to this formula?

Hope it's understandable, English is not my native language.

Upvotes: 1

Views: 130

Answers (2)

jeffreyweir
jeffreyweir

Reputation: 4824

Instead of using the .End(xlUp) method, I'd suggest that using Named Ranges and in particular the built-in structured referencing of Excel Tables/ListObjects would make for more robust and simpler code.

Using Named Ranges avoids hard-coding references in your code. If you hard code cell address into your code, those references will be pointing at the wrong place if you (or a user) later adds new rows/columns above/to the left of those hard-coded references. Using Names avoids this, and makes for more robust code.

I almost always use Excel Tables aka ListObjects to hold any data that VBA interacts with for the same reason...ListObjects are dynamic named ranges that Excel automatically expands/contracts to suit the data, and unlike .End(xlUp) they won't throw your code off if the column contains blanks.

So I'd do it like this:

Range("SomeNamedRange") = "=SUM(" & Range("SomeTable[SomeColumn]").Address & ")"

...or more likely, I'd use the [] shorthand notation:

[SomeNamedRange] = "=SUM(" & [SomeTable[SomeColumn]].Address & ")"

Upvotes: 0

nagarajannd
nagarajannd

Reputation: 715

You're missing to concatenate.

Sheets(7).Range("B" & lastrow + 1).Formula = "=SUM(b2:b" & lastrow & ")"

Upvotes: 3

Related Questions