run-time error 1004

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 whe...

excel

Macros and VBA

Sanjay Gandhi
May 30, 2018
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.

Sanjay Gandhi

Copper Contributor

May 30, 2018

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.

Lorenzo Kim

Bronze Contributor

May 30, 2018

Mr. Gandhi

It worked perfectly.

THANK YOU VERY MUCH!!

Forum Discussion

run-time error 1004