Forum Discussion
Lindsy
Oct 17, 2022Copper Contributor
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, ...
- Oct 17, 2022
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),"""")"
Lindsy
Oct 17, 2022Copper Contributor
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.
HansVogelaar
Oct 17, 2022MVP
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),"""")"
- LindsyOct 18, 2022Copper ContributorThank 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!!!