Delete N/A when using VLOOKUP

Question

I'm trying to bring the values from Sheet2 to Sheet1 using VLOOKUP formula (in VBA, after using the formula I copy paste in order to just see the value). But when the cells from Sheet2 are empty I get the "#N/A", which I don't want. What can I do to get the cell empty if there isn't any value, like replace the #N/A with nothing.

I have tried using the IFERROR formula, but I get 0 instead of nothing.

comm.Range(.Cells(2, "C"), .Cells(lr, "C")).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet2!C[-2]:C[22],3,0),"""")"

Answer 1

• You could use =IFNA instead of =IFERROR because the code in case of error generate #N/A error.
• Lastly, in your formula after the number of column you want to return (in your case column number 3) you use number 0 instead of TRUE or FALSE.

The below code is not the answer but a sample of the formula.

Code Sample:

=IFNA(VLOOKUP(A2,$D$1:$E$2,2,FALSE),"") 'In excel

.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C4:R2C5,2,FALSE)" 'In VBA