Need help with VB form and Lookup formula

[berenger]

I have a form that will search for an entered in UPC code which almost always starts with a 0. On my spreadsheet I have labeled Data and the cells are formatted as Text so the 0's show up and the next column is the description of the item.

When the user enters in the UPC number my next text box should automatically enter in the item description but it is not working it is pulling up the error message I have programmed in my code.

Someone Please help me with this code.

Private Sub UPCNum_Change()

If Len(UPCNum.Text) >= 12 Then
On Error Resume Next
x = Application.WorksheetFunction. _
VLookup(CDbl(UPCNum.Text), Worksheets("Data").Range("A2:E50000"), 2, False)
If Err.Number = 0 Then
Description = x
Else
Description = "No such product found"
End If
On Error GoTo 0
End If

End Sub

 

[borgem]

Maybe a different approach will help create a solution for you. If the UPC's are all the same length, then you could change the format of all of the UPC cells as custom with a Type: 0### or 0000. The first would make sure the leading # is either a zero of the number as entered (it would not override a leading #). The reason I might suggest the second option is that if a UPC happened to have two leading zeros, it would only show the first.

If this works for you, then the vlookup problem should go away

 

[berenger]

Not all my numbers in that column start with a 0 in it, so I am not too sure if that would work for me.

 

[borgem]

They don't have to start with 0, the most important this is whether or not they are all the same length. The leading zero from the format type would be overridden by a number if one was present.

 

[berenger]

I appreciate all the help that you are giving me. I have some UPC numbers that only have two 0's in front and some with 3 and some with 4 and so on.

so if I were to create a custom I would want to use 0#### for it to create my numbers correctly and this should help with my formula for using the vlookup?

 

[borgem]

not if they are all supposed to be the same length and you want them to display with a set number of characters. In that case I would suggest using all zeros. The lookup will see the number as being only as long as it is without the leading zeros, so if the UPC was 0001234 and the UPC 1234 was entered, it will still find it. But it will also work if they enter in 0001234.

 

[berenger]

Once again I thank you for your help. However it worked the first time but when I saved and exited the program and then reopened it I got the same response as before giving me the error message that the Product was not found.

 

[berenger]

Okay I know why this isn't working for me. Not all my numbers are the same amount of characters. They range from 8 to 14 characters in length.

 

[borgem]

I'm at a bit of a loss then. Maybe you could specify that the input be taken in and stored as text by the macro? Would that cause the lookup to function properly? You could then add in an error checker to see if their input was either exactly 8 or exactly 14 characters long and, if not, request that they enter in the leading zeros as well.

 

[berenger]

I am not sure that I know how I would edit my code to work that way. Any help would be appreciated.