Need help with VB form and Lookup formula

berenger

Board Regular
Joined
Jun 6, 2011
Messages
50
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()
Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top