Sum with dynamic cells

Natit1 · Jun 8th 2021

HI,

Can someone help and will tell me what's wrong with this command:

Code

Range("r1") = "=sum(" & Range(Cells(1, 1), Cells(Lrow, Lcol)) & ")"

Thanks

Nati

**royUK** · Jun 8th 2021

Try this

Code

Range("r1").Formula = "=SUM(Range(Cells(1, 1), Cells(Lrow, Lcol)))"

**rory** · Jun 8th 2021

If you're trying to put a formula in there, you need the address of the range:

Code

Range("r1") = "=sum(" & Range(Cells(1, 1), Cells(Lrow, Lcol)).Address & ")"

or just:

Code

Range("r1").FormulaR1C1 = "=sum(R1C1:R" & Lrow & "C" &  Lcol & ")"

If you just want the value, then:

Code

Range("r1").Value = Worksheetfunction.Sum(Range(Cells(1, 1), Cells(Lrow, Lcol)))

Natit1 · Jun 8th 2021

Sorry, but it doesn't work

The sheet is attached here.

Natit1 · Jun 8th 2021

Sorry, I answered to fast.

The second option worked great

this one - (Range("r1").FormulaR1C1 = "=sum(R1C1:R" & Lrow & "C" & Lcol & ")")

Participate now!