Vlookup in VBA only returns Error 1004

[dloskot]

I am working on a Userform and I want the user to enter their Zip Code and then I will look up the City and State. Below is the code I have been using.

Private Sub Text_Zip_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'MsgBox "this is the entered zip code " & Text_Zip & " end"

Dim zip
zip = Text_Zip.Text
City = Application.WorksheetFunction.VLookup(zip, ThisWorkbook.Sheets("Sheet3").Range("A2:C11").Value, 2, False)
MsgBox "The Zip Entered is " & zip
MsgBox "The City is " & City

End Sub

However If I put an actual zip code in the VLookup statement see below it works perfectly. I can't figure out why with can't find a match when I try to use the input from the Userform.

Private Sub Text_Zip_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'MsgBox "this is the entered zip code " & Text_Zip & " end"

Dim zip
zip = Text_Zip.Text
City = Application.WorksheetFunction.VLookup(94583, ThisWorkbook.Sheets("Sheet3").Range("A2:C11").Value, 2, False)
MsgBox "The Zip Entered is " & zip
MsgBox "The City is " & City

End Sub

I hope you can help me. I think it might have something to do with the variable being text and not number???

[InvisibleMan]

Try:

Please Login or Register  to view this content.

[dloskot]

InvisibleMan: Thanks for your quick response, but sorry for my late reply to your help. This work great, to make one change and that was add Long to the Dim zip statement.
so it read as
Dim zip As Long

Thanks again and can stop banging my head against the wall.