VBA vlookup without N/A in the following code

[Philip00]

Hello All,

I have a working macro with vlookup function and i was able to make it work that if the cell is empty then the result cell is empty as well but if the function does not find the reference cell then it gives me N/A.

Can somebody help me that instead of N/A i got blank cells as well?

Range("AQ2:AQ" & LastRow).FormulaR1C1 = "=IF(VLOOKUP(RC[-41],'" & ActiveSheet.Previous.Name & "'!R1C47:R185C2,42,0)="""","""",VLOOKUP(RC[-41],'" & ActiveSheet.Previous.Name & "'!R1C47:R185C2,42,0))"

Thank You in Advance,

 

[wideboydixon]

Try wrapping an IFERROR around it:

Range("AQ2:AQ" & LastRow).FormulaR1C1 = "=IF(IFERROR(VLOOKUP(RC[-41],'" & ActiveSheet.Previous.Name & "'!R1C47:R185C2,42,0),"""")="""","""",VLOOKUP(RC[-41],'" & ActiveSheet.Previous.Name & "'!R1C47:R185C2,42,0))"

But I guess that's also the same as:

Range("AQ2:AQ" & LastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-41],'" & ActiveSheet.Previous.Name & "'!R1C47:R185C2,42,0),"""")"

WBD