CODEWITHSUNDEEP
vbaexcel
D. Todor
D. Todor

Reputation: 157

VBA - Set Excel formula containing quotation marks as cell value

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.

Upvotes: 0

Views: 4757

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

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)"

Upvotes: 2

Related Questions