Forum Discussion
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 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)"
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 Replies
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 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.
Mr. Gandhi
It worked perfectly.
THANK YOU VERY MUCH!!
