Forum Discussion
VBA to add a column with a VLOOKUP formula referencing another sheet.
Hi, The VLOOKUP works but returns #N/A for all non matches. IFERROR results in blank cells. Dim lastRow As Long
lastRow = Cells(Rows.Count, ...
What is New Sheet? Table names cannot contain spaces...
IFERROR works like this when you specify it in VBA:
Range("AG2:AG" & lastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-19],New_Sheet[[Name]:[Exists]],5,FALSE),"""")"
The final argument of VLOOKUP is supposed to be either FALSE (only exact match) or TRUE (non-exact, closest match in a sorted table). Your formula has "3", which is probably treated as TRUE.
- Thanks, I have managed to get it working, I now face the issue of all the non-matches show as #N/A, any suggestions on a clean way around it? I have tried iferror and that kills the whole formula.
What is New Sheet? Table names cannot contain spaces...
IFERROR works like this when you specify it in VBA:
Range("AG2:AG" & lastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-19],New_Sheet[[Name]:[Exists]],5,FALSE),"""")"- Thank you, the sheet name as correct (no spaces, just changed for privacy) It was the double quotation marks I was doing wrong. Thank you so much!!!
