Search code examples
stringvbaexcelcellruntime-error

vba string assignment. WHy am I getting 1004 run time error?

On the below line in my code I am getting a 1004 run time error: Application-defined or object-defined error. I think it might be a problem on the right side of the statement. However, I've changed the way it is written a few times to no avail. As you can see I have tried inserting many character codes. What I ultimately want inserted into cell B1, where B1 is the active cell is ="'"&A1&"'," with spaces for clarity: = " ' " &A1& " ' , "

This is supposed to reference cell A1, take that value and put it inside single quotes, with a comma at the end. This is so it is formatted for an SQL statement.

Any help will be appreciated. I know this is a common error, but the error message is not helping.

ActiveCell.Formula = Chr(61) & "'" & Chr(34) & Chr(38) & "A1" & Chr(38) & Chr(34) & "',"
Solution

  • To escape the quotes you need to double them up:

    ActiveCell.Formula = "=""'"" & A1 & ""',"""