VBA - Set Excel formula containing quotation marks as cell value

0
votes

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.

1
vbaexcel
To add quotation mark use four of them ie, myString = "Adam said, " & """" & "Hello Jane!" & """" - John Muggins
The formula is fine (Assuming ";" is your delimeter as opposed to ","). A few basic checks, is ABC protected and B2 a locked cell? does the sheet "Mat_Location" exist in the workbook that is active in that point of the code (Sheets is not qualified explicitly to a specific workbook in this case) - Zerk
@JohnMuggins he's specifically said he tried that. - Zerk
Yes, now I see that. Thanks for letting me know @Zerk I wasn't even aware it was for a formula. The tags say vba and excel and that is my only expertise. I try to stay away from formulas - John Muggins

1 Answers

1
votes

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