How to insert ISNA in Vlookup VBA?

[pdmm]

Hi Everyone

Was hoping someone could help me with this little question.

I have a vlookup function in VBA and I would like to include ISNA to avoid N/A output in empty cells.
This is what the vlookup looks like:

Range("E15:E2004,I15:I2004").FormulaR1C1 = "=VLOOKUP(RC[-3],FRA!R1C8:R1031C28,21,FALSE)"
Range("N15:N2004,S15:S2004").FormulaR1C1 = "=VLOOKUP(RC[-4],FRA!R1C8:R1031C28,21,FALSE)"


Thank you kindly in advance!

Best regards
Daniel

[jindon]

IfError?

"=iferror(VLOOKUP(RC[-3],FRA!R1C8:R1031C28,21,FALSE),"""")"

[benishiryo]

hi daniel. there's a better way to ISNA. it's IFERROR (in Excel 2007 and above) or IFNA (in excel 2013 and above). but to do ISNA, simply cover it over the formula:

Please Login or Register  to view this content.

you could do it shorter with IFERROR or IFNA:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[pdmm]

IfError?

"=iferror(VLOOKUP(RC[-3],FRA!R1C8:R1031C28,21,FALSE),"""")"

my hero!!! Thank you. Was much easier than I thought it would be