What is proper syntax for formula contained in macro?

[imimin]

Greetings!

How would I properly syntax the following for the expression (formula) on line 1 (I now have it between the double quotes)? As it is, I am getting a 1004 error 'Application-defined or object-defined error'.

Range("C2").Value = "=IF(COUNTIF(Sheet2!$A$2:$A$10000,A2),VLOOKUP(A2,Sheet2!$A$2:$B$10000,2,FALSE),"")"
Range("C2").AutoFill Destination:=Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row), Type:=xlFillSeries

Thank you!

 

[Joe4]

Here is one way:

Range("C2").Formula = "=IF(COUNTIF(Sheet2!$A$2:$A$10000,A2),VLOOKUP(A2,Sheet2!$A$2:$B$10000,2,FALSE)," & Chr(34) & Chr(34) & ")"

 

[imimin]

Thanks for your quick reply! That did the trick!

What does the " & Chr(34) & Chr(34) & " do???

 

[Joe4]

The problem is that the double-quotes (") are used as text qualifiers in writing the formulas in VBA, but you actually also want literal double-quotes within your formula, so writing the formula in VBA can be a little tricky.

If you double or triple up the double-quotes, you can get it to work. Personally, I find that a bit confusing and tough to read. The ASCII code for double quotes-is number 34. So CHR(34) is the ASCII code characterization for double-quotes. So:
Chr(34) & Chr(34)
returns two double-quotes.

 

[imimin]

Thank you! That helps a lot and you did well explaining it !