Solved: Vlookup/Match

[CharlesH]

Hi,

I need a little bit of help. I'm trying to use the "Vlookup" or "Match" worksheet function in my code for a userform textbox. I can set either Vlookup or Match to a reference and the code will work, however when I use the "Userform1.Textbox1" in the code both Vlookup and Match fails.
The following is the code I'm trying to use.
You will note the "Range("B2") in the code this works and as mentioned when I try to replace it with "myval" and or Userform2.Textbox1 the code fails.
The TextBox can be numeric and of allphanumeric and the range in the "Stock" worksheet will be in B2 thru B8.

[vba]
Private Sub CommandButton39_Click()
Dim LookupData As Variant
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim myval As Variant
myval = UserForm2.TextBox1
'''' validate textbox1 for code if user code ok, if barcode call FindProduct and hide userform2 ''
' we will use a vlookup for code ''
LookupData = Application.WorksheetFunction.VLookup(Range("B2"), Worksheets("Stock").Range("b1:b8"), 1, False)
If IsError(LookupData) Then
Msg = "The Bar Code " & myval & " you entered is not linked to a code do you wish to link ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Missing Barcode" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Unload UserForm2
Cfind = True
frmBarcode.Show ' Perform some action.
Else ' User chose No.
Exit Sub ' Perform some action.
End If
[/vba]
Any help would be appreciated. Also when the code runs I do not hide nor unload the userform.(I do not know for sure if its necessary to do this)
You will note I have the code for Vlookup, but even when I code for the "Match" it fails.

[CharlesH]

Hi,

Thanks to all who looked at this thread. I solved my problem, and was the range I was looking in was Numeric and I did not code for it. I made the assumption that it didn't matter if it was numeric or alphanumeric.