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