run-time error 1004

[Lorenzo Kim]

I am trying to place a total after the end of a column data (the end record is variable) with the following code, I keep getting a run error of 1004 - I think the "LastRow" is the culprit because when I substituted it with a number - the sub worked.

many thanks

Range("A1").Select
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With
Range("A" & LastRow + 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-LastRow]C:R[-1]C)"

[#2 · Accepted solution]

Hello Lorenzo,

Please make this correction:

ActiveCell.FormulaR1C1 = "=SUM(R[-" & Trim(Str(LastRow)) & "]C:R[-1]C)"

Since we are writing a string in .FormulaR1C1, therefore the value we are passing being a variable needs to be negotiated independently and not in a string. So when we put a number directly it takes the value directly, whereas when we write a variable, it needs to retrieve its value, which in string reads as string LastRow. Giving it independent of string allows to take its value.

Hope this helps.

[#3]

Mr. Gandhi

It worked perfectly.

THANK YOU VERY MUCH!!

[#4]

---

@Sanjay Gandhi wrote:

Hello Lorenzo,

Please make this correction:

ActiveCell.FormulaR1C1 = "=SUM(R[-" & Trim(Str(LastRow)) & "]C:R[-1]C)"

Since we are writing a string in .FormulaR1C1, therefore the value we are passing being a variable needs to be negotiated independently and not in a string. So when we put a number directly it takes the value directly, whereas when we write a variable, it needs to retrieve its value, which in string reads as string LastRow. Giving it independent of string allows to take its value.

Hope this helps.

---

I having Problem with this and i couldnt post in this community, sorry if i violates the rule.