Menu
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!
938
Strings in VBA are delimited with quotation marks. If you want to include quotation marks in your string, you need to double them. Otherwise, the VBA compiler will not recognize that they should not end the string. aStringVariable = "The word ""quotes"" is in quotes."
I find the easiest way is to double up on the quotes to handle a quote. *Note: CHAR() is used as an Excel cell formula, e.g. writing "=CHAR(34)" in a cell, but for VBA code you use the CHR() function.
To include double quotes inside a formula, you can use additional double quotes as escape characters. By escaping a character, you are telling Excel to treat the " character as literal text. You'll also need to include double quotes wherever you would normally in a formula.
Whenever in doubt, record a macro if it allows :)
Try this
.Range("A2").Formula = "=IF(Pivot!A5="""",A1,Pivot!A5)"
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)"
you might need to do this:
.Range("A2").Formula = "=IF(Pivot!A5="& """" & """" & ",A1,Pivot!A5)"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
