CODEWITHSUNDEEP
excelexcel-2007vba
GeoffDS
GeoffDS

Reputation: 1271

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

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!

Upvotes: 19

Views: 107327

Answers (3)

Marc
Marc

Reputation: 11613

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

Upvotes: 12

Greg
Greg

Reputation: 8784

you might need to do this:

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

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

Try this

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

Upvotes: 39

Related Questions