CODEWITHSUNDEEP
vbaexcel
whydoieven
whydoieven

Reputation: 599

Getting around quotes inside Excel VBA

I have a Excel Formula that I'm using inside VBA. However, when I'm compiling it, VBA throws me an error.

I'm kind of certain that it is because of the quotes in my Formula. 

Range("E2:E" & lastRow).FormulaLocal = "=IFERROR(IF(VLOOKUP(D2,$B:$B,1,FALSE)=D2,"Form Present"),"Not Present")"

This is what I tried to get around this, 

 Range("E2:E" & lastRow).FormulaLocal = "=IFERROR(IF(VLOOKUP(D2,$B:$B,1,FALSE)=D2,&Chr(34)&Form Present&Chr(34)&),&Chr(34)&Not Present&Chr(34)&)"

I used chr(34) to generate a Quote symbol within however this is not working. Any workaround for this?

Upvotes: 1

Views: 85

Answers (2)

Mrig
Mrig

Reputation: 11727

Either use 

Range("E2:E" & lastRow).FormulaLocal = "=IFERROR(IF(VLOOKUP(D2,$B:$B,1,FALSE)=D2,""Form Present""),""Not Present"")"

or

Range("E2:E" & lastRow).FormulaLocal = "=IFERROR(IF(VLOOKUP(D2,$B:$B,1,FALSE)=D2," & Chr(34) & "Form Present" & Chr(34) & ")," & Chr(34) & "Not Present" & Chr(34) & ")"

Upvotes: 2

Zerk
Zerk

Reputation: 1593

Just double up quotation marks and change it to this:

 Range("E2:E" & lastRow).FormulaLocal "=IFERROR(IF(VLOOKUP(D2,$B:$B,1,FALSE)=D2,""Form Present""),""Not Present"")"

This fails "=IF(1=1,"Y","N")" , whereas this works "=IF(1=1,""Y"",""N"")"

Upvotes: 2

Related Questions