VBA - Set Excel formula containing quotation marks

Question

I am trying to get my VB script to enter an Excel formula into a cell. Unfortunately I had no luck so far.

    Sheets("ABC").Select
Range("B2").Value = "=IF(Mat_Location!F2=0;"";Mat_Location!F2)"

After some googling and searching on this site I figured it would be because of the quotation marks and so I tried changing the quotation marks to double quotation marks.

    Sheets("ABC").Select
Range("B2").Value = "=IF(Mat_Location!F2=0;"""";Mat_Location!F2)"

That also didn't help.

Any help is appreciated. Thanks in advance.

Answer 1

Replace your multiple " with Chr(34), it's easier to debug it this way.

Also, there's no need to Select the sheet first.

Sheets("ABC").Range("B2").FormulaLocal = "=IF(Mat_Location!F2=0;" & Chr(34) & Chr(34) & ";Mat_Location!F2)"

Note: it seems your Excel's regional settings is having ; as a delimeter inside the formula. The default settings is , , so for my Excel setting (maybe also yours) it might be:

Sheets("ABC").Range("B2").Formula = "=IF(Mat_Location!F2=0," & Chr(34) & Chr(34) & ",Mat_Location!F2)"