SOLVED

VBA to add a column with a VLOOKUP formula referencing another sheet.

Copper Contributor

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)"
  

 

 

 

 

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.
best response confirmed by Lindsy (Copper Contributor)
Solution

@Lindsy 

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!!!
1 best response

Accepted Solutions
best response confirmed by Lindsy (Copper Contributor)
Solution

@Lindsy 

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),"""")"

View solution in original post