Run-time error 1004 with ActiveCell.Formula command

[gtdp]

Hello everyone! I'm having an incredibly frustrating problem with VBA throwing an application-defined or object-defined error when trying to run the following code (I've cut out some irrelevant bits):

Dim OriginalPrice As String
OriginalPrice = ActiveCell.Value
Sheets("Calculations").Select
ActiveCell.Formula = "=LEFT(" & OriginalPrice & ",SEARCH(""euro""," & OriginalPrice & ")-2)"

The fourth line is throwing the error. I have a feeling it's got something to do with the OriginalPrice variable being a string, as a couple of lines later I have a similar line:

ActiveCell.FormulaR1C1 = "=ROUNDUP(" & ConversionFactor & "*Calculations!RC[-1],2)"

which works fine. Additionally, I can replace OriginalPrice (a String) with ConversionFactor (an Integer) in the line of code causing an error, and the error goes away (useful for debugging, but not useful for actually running!) Should I be using a different variable type for inserting variables into ActiveCell.Formula lines when the variable contains letters as well as numbers?

Anyone have any idea how to fix this? Searching around says most people solved the problem by switching from using ActiveCell.Formula to ActiveCell.FormulaR1C1 or vice-versa, but this hasn't solved anything for me. I'd be grateful for any help!

 

[Andrew Poulsom]

Welcome to MrExcel.

More quotes needed:

ActiveCell.Formula = "=LEFT(""" & OriginalPrice & """,SEARCH(""euro"",""" & OriginalPrice & """)-2)"

 

[gtdp]

Fantastic, that's fixed it completely and it's now working as intended. I'm at a loss as to why those extra quotes are needed, but I'm relieved that it's something relatively easy to fix! Thank you very much for your speedy and concise help, you're a life-saver