Excel (2007) VBA - .Formula with quotes in it

Question

I tried putting the following code into a program yesterday. VBA called an error. I assume it is because of the double quotes inside the formula. I googled and all results I found just gave the basic of putting formulas in, but none explained how to get around quotes inside.

(there was a With statement before this, Pivot is a worksheet name)

.Range("A2").Formula = "=IF(Pivot!A5="",A1,Pivot!A5)"

Any help is much appreciated. Thanks!

score 37 · Accepted Answer · answered Apr 13 '12 at 14:15

37

Whenever in doubt, record a macro if it allows :)

Try this

.Range("A2").Formula = "=IF(Pivot!A5="""",A1,Pivot!A5)"

answered Apr 13 '12 at 14:15

Siddharth Rout

147,039
17
206
250

Good point about the macro recorder. Also, your code works and is the cleanest of all answers. Thanks. – GeoffDS Apr 19 '12 at 15:38

score 11 · Answer 2 · answered Apr 13 '12 at 14:39

11

Use Chr(34) in place of a double-quote.

So in your case:

.Range("A2").Formula = "=IF(Pivot!A5=" & Chr(34) & Chr(34) & ",A1,Pivot!A5)"

answered Apr 13 '12 at 14:39

Marc

11,403
2
35
45

This solution will work more constantly than any of the other submissions. – Matthew Withrow Jun 18 '21 at 17:40

score 1 · Answer 3 · answered Apr 13 '12 at 14:25

1

you might need to do this:

.Range("A2").Formula = "=IF(Pivot!A5="& """" & """" & ",A1,Pivot!A5)"

answered Apr 13 '12 at 14:25

Greg

8,574
21
67
109

score 0 · Answer 4 · answered May 23 '23 at 15:49

The usual case is that you have a (possibly complex) formula with double quotes in it and you know it works, but just want to have VBA drop it into a cell, using:

ThisWorkbook.Sheets("MySheet").Range("A1").Formula = "=TheFormulaWithCHR(34)substituted in for the double quotes"

The easy way to create is to take the formula you have with the double quotes and use Find/replace in Notepad or a similar basic test editor:

Find

"

Replace with

" & CHR(34) & "

and you will then have a working formula. That's much easier than rebuilding the original formula to use CHR(34) manually, particularly if it's an expression incorporating several double quotes. To tidy up instances where the original formula had two double quotes together:

Find

& "" &

Replace with

&

It obvs doesn't matter if you CONCAT an empty string into the expression - your formula will work regardless - but it's tidier not to. Two Find/Replace operations, job done.

Excel (2007) VBA - .Formula with quotes in it

4 Answers4

Linked