Application Worksheet Function Vlookup error

[Neal]

Dear Experts,

I am trying to use a Vlookup in a worksheet_change macro
and am getting an error message when I use the
range_lookup value of FALSE. Can this be used in a macro
or what am I doing wrong. I need to make sure that the
vlookup is an exact match. Here is the code. Thanks in
advance.

Neal

Private Sub Worksheet_Change(ByVal target As Range)
'
' Test input into symbol cell on YF page
'
Application.EnableEvents = False
If target = Range("InputCell") Then 'make target match
specific cell
If IsError(Application.WorksheetFunction.VLookup(Range
("InputCell"), Worksheets("Sheet2").Range("A5:B12"), 2,
False)) Then
MsgBox "It is an invalid name"
Range("InputCell").ClearContents
Else
If Not IsError(Range("companyinput")) Then
Range("InputCell").Value = UCase(Range
("InputCell").Value)
'Add the other code from the Macro that moves the cell
to
'the percent cell
Range("percentinput").Select
End If
End If
End If

Application.EnableEvents = True

End Sub

 

[Nigel]

Are there duplicate values in your table? - this will produce an error even
if there is an exact match.
Cheers
Nigel

 

[Bob Phillips]

Neal,

Just use Application.Vlookuup.

Also, you need to put WorksheetFunction on IsError

Private Sub Worksheet_Change(ByVal target As Range)
'
' Test input into symbol cell on YF page
'
Application.EnableEvents = False
If target = Range("InputCell") Then 'make target match specific cell
If WorksheetFunction.IsError(Application.VLookup(Range("InputCell"),
Worksheets("Sheet2").Range("A5:B12"), 2, False)) Then
MsgBox "It is an invalid name"
Range("InputCell").ClearContents
Else
If Not WorksheetFunction.IsError(Range("companyinput")) Then
Range("InputCell").Value = UCase(Range("InputCell").Value)
'Add the other code from the Macro that moves the cell to
'the percent cell
Range("percentinput").Select
End If
End If
End If

Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Neal]

Bob,

Thanks very much for the answer. That is exactly what I
need.

Neal

 

[Tom Ogilvy]

While the worksheetfunction object does support the iserror function used in
Excel, VBA has its own IsError Function applicable to the use of
Application.Vlookup. So the worksheetfunction qualifier is not required in
this case.


? iserror(cvErr(xlErrNA))
True
? worksheetFunction.IsError(cvErr(xlErrNa))
True