Inserting double quotes string within formula within VBA (Excel

Question

I would like to insert a function looking like this, using VBA:

=IF(ISERROR(VLOOKUP(B12,AASD!A:B,2,0)),"Check #",VLOOKUP(B12,AASD!A:B,2,0))

Code from VBA:

Sheets("Sheet1").Range("C12").Formula = "=IF(ISERROR(VLOOKUP(B12,AASD!A:B,2,0)),"Check AA#",VLOOKUP(B12,AASD!A:B,2,0))"

Issue is the "" quotes for inserting the string in the IF formula messes up the "" quotes in VBA.

Is there a clever work around for this?

SJR · Accepted Answer · 2018-06-08 21:31:40Z

4

Just double them up

Sheets("Sheet1").Range("C12").Formula = "=IF(ISERROR(VLOOKUP(B12,AASD!A:B,2,0)),""Check AA#"",VLOOKUP(B12,AASD!A:B,2,0))"

You can also use Chr(34) in place of a single set of quotes.

answered Jun 8, 2018 at 21:31

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2018-06-08 21:33:26Z

3

Additionally, that formula can be cut in half with IFERROR.

Sheets("Sheet1").Range("C12").Formula = _
   "=IFERROR(VLOOKUP(B12, AASD!A:B, 2, 0), ""Check AA#"")"

answered Jun 8, 2018 at 21:33

user4039065