Delete N/A when using VLOOKUP

1
votes

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),"""")"
1
excelvbaexcel-formula
Works fine for me. You will get a 0 if the cell has a 0 else you will get a "blank value" - Siddharth Rout
What happens if you actually try that formula directly in a cell? - Zac
@Zac if works by using it directly in the cell, but I cannot. The client uses an userform to complete the empty data after the VLOOKUP, this is why I used it in VBA, because after I copy paste it to remove the formula - Anca Vulc

1 Answers

1
votes
  • 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