Forum Discussion
Solved
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, 2).End(xlUp).Row
Range("AF2:AF" & lastRow).FormulaR1C1 = "=IF(AND(RC[-9]>350, RC[-11]>25, RC[-11]<90), IF(COUNTIF(C[-15], RC[-15])>1, IF(LARGE(C[-31],1)=RC[-31], ""Duplicate Check"", """"),""""),"""")"
Range("AF2:AF" & lastRow).Value = Range("AF2:AF" & lastRow).Formula
Range("AG2:AG" & lastRow).FormulaR1C1 = "=VLOOKUP(RC[-19],New Sheet[[Name]:[Exists]],5,0)"
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),"""")"
4 Replies
- 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),"""")"
