VlookUp error

[Whistler]

HI I'm trying to add function to my input form that when user puts ID number in to IDBox, the name come up automaticly in to NameBox.

aTable is my Table where I have ID in first column and name in second.

Private Sub IDBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim ws As Worksheet
Set ws = Worksheets("NamesBase")
Dim aTable As Range
Dim IDBoxValue As String
Dim NameFromID As String
IDBoxValue = IDBox.Value
Set aTable = ws.Range("A:B")

NameFromID = Application.WorksheetFunction.VLookup(IDBoxValue, aTable, 2, False)
NameBox.Value = NameFromID
End Sub

I'm getting "Unable to get the Vlookup property of the WorksheetFunction class" error


Thanks for your help.

 

[VoG]

That means that it isn't finding a match.

Are the values in column A strings or numbers?

 

[Richard Schollar]

Hi

What are your ID values in the first column? Are they numbers? This might explain why you get no match even when you think there should be one. You need to ensure IDBoxValue is also a number in which case (and not a string).

 

[Whistler]

Hi, Thanks for the answer. The column A is a type General, but there are numbers like 54180. Thanks

 

[VoG]

Try

Private Sub IDBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim ws As Worksheet
Set ws = Worksheets("NamesBase")
Dim aTable As Range
Dim IDBoxValue As Variant
Dim NameFromID As String
IDBoxValue = Val(IDBox.Value)
Set aTable = ws.Range("A:B")

NameFromID = Application.VLookup(IDBoxValue, aTable, 2, False)
NameBox.Value = NameFromID
End Sub

 

[Richard Schollar]

Are the values in column A only composed of digits (0-9) and nothing else? If so, then you can amend your code to the below:

Private Sub IDBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim ws As Worksheet
Set ws = Worksheets("NamesBase")
Dim aTable As Range
Dim IDBoxValue As Long
Dim NameFromID As String
IDBoxValue = Val(IDBox.Value)
Set aTable = ws.Range("A:B")

NameFromID = Application.WorksheetFunction.VLookup(IDBoxValue, aTable, 2, False)
NameBox.Value = NameFromID
End Sub

If, however, they are a mixture of alphanumeric characters, then I think it might be best to convert all the values in column A of your sheet to text values (format the column as Text via Format>Cells>Number tab, and then select the column and go Data>TextToColumns>Delimited>Next>Next and make sure the you choose an import format of Text>Finish). The vlookup should then work.

 

[Whistler]

I have change IDBoxValue type to Long and it's works!

Thanks Guys!