Unable to match property error

[Guest]

I need some help with my code. This code works perfect except for when
someone types in the cell. When someone types in the cell it causes an unable
to match error. What code could I use to trap the error and prehaps clear the
cell (or open to suggestions) before the match function is used?

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.WorksheetFunction.Match(Target.Value,
Worksheets("Codes").Range("B:B"), 0)
End If
Application.EnableEvents = True

 

[papou]

Hello Kenny
Try this:
If Target.Cells.Count = 1 And Target.Value <> "" And Target.Column = 9 Then
On Error Resume Next
dummy = Application.WorksheetFunction.Match(Target.Value,
Worksheets("Codes").Range("B:B"), 0)
If Err <> 0 Then
MsgBox "No matching value found"
Err.Clear
On Error GoTo 0
Else
Application.EnableEvents = False
Target.Value = dummy
Application.EnableEvents = True
End If

End If

HTH
Cordially
Pascal