SOLVED
Home

run-time error 1004

Lorenzo Kim
Valued Contributor

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)"

 

 

 

 

3 Replies
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.

Mr. Gandhi

 

It worked perfectly.

THANK YOU VERY MUCH!!

Image(1).PNG


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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies