Vlookup #N/A

[Forestq]

Team,

I have code where I'm using vlookup function to compare two sheets. Below code works god:

Sub vlookup_copy()
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim strFile As String
    Dim Rng As String
    Dim sfullpath As String
 
 
    Set wksDest = Worksheets("Sheet1")
 
    MsgBox "Open file with source data"
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
 
    If strFile = "False" Then
        Exit Sub
    End If
 
    Set wkbSource = Workbooks.Open(strFile)
    Set wksSource = wkbSource.Worksheets("Sheet1")
 
    With wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Address
    End With
 
    sfullpath = "[" & wksSource.Parent.Name & "]" & wksSource.Name
 
 
    wksDest.Activate
    x_rows = Application.WorksheetFunction.CountA(Columns(1))
 
    Range("B1").Select
    ActiveCell.Formula = "=VLOOKUP(A1,'" & sfullpath & "'!" & Rng & ",4,0)"
 
    Selection.AutoFill Destination:=Range("B1:B" & x_rows), Type:=xlFillDefault
 
    Application.DisplayAlerts = False
    wkbSource.Close
    Set wkbSource = Nothing
    Set wksSource = Nothing
    Application.DisplayAlerts = True
 
End Sub

But when looking value does not exist in array, macro display result as "#NA". Please help me, how to change the code. I would like for example to display note like "looking value not found".

king regards,
PvK

 

[drsarao]

Forestq,
Add error check in the formula itself.
Thus:
ActiveCell.Formula = "=IF(ISERROR(VLOOKUP(A1," & sfullpath & "!" & Rng & ",4,0)),""Lookup value not found"",VLOOKUP(A1," & sfullpath & "!" & Rng & ",4,0))"

(in Excel 2007 and above you can use IFERROR() to make it better readable)

 

[Forestq]

Thanks so much drsarao, works great.

Also, when I run function from workbook, I can do something like this and works also great .
Thanks for help once again!

= IF(ISNA(VLOOKUP(A1,Sheet2!$A:$C,2,FALSE)),"NOT FOUND",VLOOKUP(A1,Sheet2!$A:$C,2,FALSE))

regards,
PvK

 

[drsarao]

You are welcome.
In fact, ISNA() is better in this particular case.
Because ISERROR() reacts to ALL the errors eg #NUM,#VALUE etc.

 

[Forestq]

god to know! thansk for information.