VBA Run-time error 1004 caused by blank vlookup value

Hi

I’m looking for some help.

I have a user form which contains two vlookup's. The purpose of the form is lookup a post code entered in a text box and confirm, if the post code is available in the list and if so, what products are available in this postal region and who can provide it.

When I enter a post code the form either provides me with the relevant details regarding that region or a message box appears to advise the post code is not listed. This is fine and exactly what I am looking for. I have a button to clear the user form and once this has been selected and I click in the empty text box where I want to enter a new post code, I get the following error message:

Run-time error '1004'

Unable to get the VLookup property of the WorksheetFunction class

From looking at similar posts online, it appears that the issues possibly lies with the text box attempting to lookup a blank value which I want to avoid; however I am a total novice in regards to VBA and can’t take the next step to amend the code.

Can anyone help adjust my code to stop the ProductBox and PartnerBox attempting to look up a value when I click on the PostCodeBox after clearing the form?

Private Sub PostCodeBox_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet21.Range("A:A"), Me.PostCodeBox.Value) = 0 Then
MsgBox "No Products Available In This Post Code District"
Me.PostCodeBox.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.ProductBox = Application.WorksheetFunction.VLookup((Me.PostCodeBox), Sheet21.Range("Lookup"), 2, 0)
.PartnerBox = Application.WorksheetFunction.VLookup((Me.PostCodeBox), Sheet21.Range("Lookup"), 3, 0)
End With
End Sub

Try adding "On Error Resume Next" at the top of your macro :

Please Login or Register  to view this content.

If that doesn't work, please post your project for review.

Thanks for all the help, I went with bakerman2's code and it works perfectly. Really appreciate it, thanks again