VBA to Exit Sub if VlookUp not returned.... I think!?!

[Richard_mcr]

Hi All,

I have a VlookUp to return project data based on User form txtInput_Change, in the way of a project number. It works just great with projects in the list, however I need to allow the user to use a project number not in the range at times. In this scenario, I return an error.

I've tried adapting serval codes found on the forum, but to no success. I feel like I need an IfError Exit Sub of some sort...!??

Any pointers would be much appreciated!

Working code when project number is valid:


Private Sub txtPrNu_Change()

If Len(txtPrNu.value) = 5 Then

txtPrNa = Application.WorksheetFunction.Vlookup(txtPrNu.value, Worksheets("ProjectData").Range("A:C"), 3, False)
txtClnt = Application.WorksheetFunction.Vlookup(txtPrNu.value, Worksheets("ProjectData").Range("A:C"), 2, False)

End If

End Sub

 

[iggydarsa]

If Len(txtPrNu.Value) = 5 Then
    On Error Resume Next
    txtPrNa = "" 'if value not found leave it blank
    txtPrNa = Application.WorksheetFunction.VLookup(txtPrNu.Value, Worksheets("ProjectData").Range("A:C"), 3, False)
    
    txtClnt = "" 'if value not found leave it blank
    txtClnt = Application.WorksheetFunction.VLookup(txtPrNu.Value, Worksheets("ProjectData").Range("A:C"), 2, False)
End If

 

[Richard_mcr]

Sorry for the rubbish response!

Thanks very much!