Unable to get the VLookup property of the WorksheetFunction class

I want to implement a barcode finder in excel, so I have item names and barcodes in separate sheets depending on what group an item belongs to. The user clicks on a radio button (selecting Group 1, Group 2, or whatever) and a list box's fill range is set to one of the dynamic name ranges I have defined.

The user then click on a button in order to copy the barcode for the corrseponding item. Here is the macro associated with the button:

Sub Get_Barcode() Dim objData As New DataObject Dim barcode As String On Error GoTo Errorcatch barcode = Application.WorksheetFunction.VLookup(ListBox1.Text, ListBox1.ListFillRange, 1, False) objData.SetText barcode objData.PutInClipboard MsgBox "Barcode " & barcode & " copied to clipboard"
Errorcatch:
MsgBox Err.Description
End Sub

The problem is that it displays a Unable to get the VLookup property of the WorksheetFunction class and I don't know what I am doing wrong ...

1 Answer

Solved it.

Changed corresponding line in original code with following:

barcode=Application.VLookup(ListBox1.Text,ActiveWorkbook.Names(ListBox1.ListFillRange).RefersToRange, 2, False)