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 int开发者_StackOverflow中文版o 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) & "',"

To escape the quotes you need to double them up:

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

I believe you are missing your first opening quote and your last closing quote. Directly translating your code (with space for clarity), you have the string below:

= ' " & A1 & " ,

Below is the correct syntax for your intended formula

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

I took out the Character Code calls for non-quotations to simplify things, but it is not required.