VBA line entering formula not working

[BruceEdwards]

I am using the line of code below to enter a formula to remove the "]" character from a column. The formula below will work when entered manually, but when I run the macro, I get an error message.

Range("H2:H" & FinalRow).FormulaR1C1 = "=SUBSTITUTE(RIGHT(RC[-1],7),CHAR(93),"")"

Error message I get:

Run-Time error '1004':
Application-defined or object-defined error.


Thanks for any assistance.

 

[mrshl9898]

You're missing some quotation marks.

Range("H2:H" & finalrow).FormulaR1C1 = "=SUBSTITUTE(RIGHT(RC[-1],7),CHAR(93),"""")"

 

[BruceEdwards]

Perfect! Thanks so much.

 

[Trebor76]

Here's the same without the R1C1 reference style which in my humble opinion is far easier to read / follow:

Range("H2:H" & FinalRow).Formula = "=SUBSTITUTE(RIGHT(G2,7),CHAR(93),"""")"

Regards,

Robert