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

 

[S Nik]

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?

In sheet1 I have "Vac_List" name range that its column A is code (16 digit number) and column B is the item name. I want to see item name in my userform, if first 16 digit of barcode is same as column A.

Sorry if it is confusing.

 

[steve the fish]

Yes i know what you are after but i cant see your workbook so im trying to single out the error. Tell me the address of the named range.

 

[S Nik]

Yes i know what you are after but i cant see your workbook so im trying to single out the error. Tell me the address of the named range.

I know it is hard without seeing the worksheet. I appreciate your help. The range is sheet1 A3:B20.

 

[S Nik]

In fact I have a worksheet and range name as "Vac_List." Do you think that causes the problem?

 

[steve the fish]

So the error is saying it cant find the lookup value in A3:A20. When dealing with numbers as lookup values you have to be certain you are comparing like with like ie true numbers in the lookup value and lookup array not text numbers

 

[steve the fish]

You said it was in Sheet1 just? Tell you what you could do. Go to a completely new sheet and type out a VLOOKUP formula that works using the barcode and the named range. Copy it and paste it here.

 

[Norie]

Please don't cross-post without providing a link to the thread on the other forum.

 

[S Nik]

Please don't cross-post without ..

I didn't know this two forum are same.

 

[S Nik]

You said it was in Sheet1 just? Tell you what you could do. Go to a completely new sheet and type out a VLOOKUP formula that works using the barcode and the named range. Copy it and paste it here.

I can not attach my worksheet here. But if you check this link (Post# 13), you can open the excel file if it helps.

https://www.excelforum.com/excel-programming-vba-macros/1217042-vlookup-error-in-the-userform.html

 

[Norie]

S Nik

They are not the same, that's kind of the point.