Using IsNA to test VLookup in VBA code.

[KMBSmith]

Hi! I am trying to run a macro in which the entry in a text box is searched for in a specified range and then entered into a given cell if found. However, if the value is not found, I would like a message box to appear alerting me to this fact.
The problem I have is that the IsNA function does not seem to work in my VBA code. The macro works if the value is found but I get a run-time error if not.
I have a simplified version of what I am trying to do shown below. If anyone could suggest a solution or an alternative method to make this macro work, I think I would be able to apply this to my other macro. Thanks in advance.

Sub Test()
Item = Range("D1")
List = Range("A1:B10")
Search = WorksheetFunction.VLookup(Item, List, 2, False)
If WorksheetFunction.IsNA(Search) Then
Range("D7") = "NOT FOUND"
Else
Range("D7") = Search
End If
End Sub

 

[Sal Paradise]

Try this?

Sub Test()
    Item = Range("D1")
    List = Range("A1:B10")
    If WorksheetFunction.IsNA(WorksheetFunction.VLookup(Item, List, 2, False)) Then
        Range("D7") = "NOT FOUND"
    Else
        Range("D7") = Search
    End If
End Sub

 

[Legacy 68668]

try

Sub Test()
    Item = Range("D1")
    List = Range("A1:B10")
    Search = Application.VLookup(Item, List, 2, False)
    If IsError(Search) Then
        Range("D7") = "NOT FOUND"
    Else
        Range("D7") = Search
    End If
End Sub

 

[KMBSmith]

Thanks Seiya! This works, although I'm not sure why replacing the WorksheetFunction part with Application before VLookup seems to have been the answer!

 

[Legacy 68668]

Thanks Seiya! This works, although I'm not sure why replacing the WorksheetFunction part with Application before VLookup seems to have been the answer!

WorksheetFunction raise the Runtime Error when the function like Match, Lookup, etc. doesn't find the match, whereas Application.VLookUp, which left from the reason to have a compatibility with old version, retunrs error, so that you can evaluate by IsError function.

If you want to use WorksheetFunction then you could write like this

Sub Test()
Item = Range("D1")
List = Range("A1:B10")
On Error Resume Next
Search = WorksheetFunction.VLookup(Item, List, 2, False)
If Err <> 0 Then
    Range("D7") = "NOT FOUND"
Else
    Range("D7") = Search
End If
On Error GoTo 0
End Sub

 

[elitecop]

Hi all,

hi all,
im trying macro see below,
in excel formula "=IF(ISNA(VLOOKUP(C4;G4:G12;1;0));IF(AND(D4>=0.791666667;D4<=1.0833217593);"PT";"OPT");"Özel")"

VBA codes

Range("CV2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-94]=""Televizyon"",IF(ISNA(VLOOKUP(RC[-71],Kodlama!R18C6:R200C6,1,0),IF(AND((RC[-74]>=0.791666667,RC[-74]<=1.0833217593),""PT"",""OPT""),""ÖZEL""))"