VLookup error in the Userform

[S Nik]

I created an inventory worksheet with userforms. Now I have a problem with VLookup command. I want the name of the item is shown in the "txtName" after I scanned the barcode in the "txt2DBarcode."

Everything looks fine till I send the information. Then I receive an error.
Run-time error '1004': Unable to get the VLookup property of the worksheetFunction class.

Private Sub txt2DBarcode_A_afterupdate()      
    If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Left(Me.txt2DBarcode_A.Text, 16)) = 0 Then
        MsgBox "This is an incorrect Barcode!"
        Me.txt2DBarcode_A.Value = ""
    Exit Sub
    End If
    
    With Me
    .txtName = Application.WorksheetFunction.VLookup(Left(txt2DBarcode_A.Text, 16), Sheet1.Range("Vac_List"), 2, 0)
    End With
 End Sub

 

[steve the fish]

Are your barcodes numbers?

 

[S Nik]

Yes. I wish I could send a screenshot. The first 16 digit of "txt2DBarcode" is my item id that is listed on sheet1 range A:A.

 

[steve the fish]

Try:

.txtName = Application.WorksheetFunction.VLookup(Val(Left(txt2DBarcode_A.Text, 16)), Sheet1.Range("Vac_List"), 2, 0)

 

[S Nik]

Try:

.txtName = Application.WorksheetFunction.VLookup(Val(Left(txt2DBarcode_A.Text, 16)), Sheet1.Range("Vac_List"), 2, 0)

Thanks Steve but still have debug.

 

[steve the fish]

Ok lets break it down. Run these lines just before your line thats failing:

msgbox Val(Left(txt2DBarcode_A.Text, 16))
msgbox Sheet1.Name
msgbox Range("Vac_List").parent.name
msgbox Range("Vac_List").address

Paste the results

 

[S Nik]

Ok lets break it down. Run these lines just before your line thats failing:

msgbox Val(Left(txt2DBarcode_A.Text, 16))
msgbox Sheet1.Name
msgbox Range("Vac_List").parent.name
msgbox Range("Vac_List").address

Paste the results

It is showing me 3 message boxes with the same error at the end.

 

[steve the fish]

Dont know what you mean by that. What do the messageboxes say?

 

[S Nik]

Dont know what you mean by that. What do the messageboxes say?

The first message box is the first 15 digit number from "txt2DBarcode"
The second and third are "Vac_List"

 

[steve the fish]

So in sheet "Vac_List" you have a named range "Vac_List". What is the address of this named range? Does it have your lookup value from txt2DBarcode in the first column of this range?