VLOOKUP on userform only working if there's a letter in the lookup data?

[mageeg]

Got a weird one for you, hopefully it's something obvious.

I've got a table that consists of 3 columns; Batch Number, Job Number, Date of manufacture

I have one textbox for each; textbox1, textbox2, textbox3.

I want to type in the Batch number in Textbox1, then use vlookup to return the Job Number and Date of manufacture.

I have written the following code to do this:

Private Sub TextBox9_AfterUpdate()

Dim MyTableArray As Range

Set MyTableArray = Sheets("Sheet2").Range("A:F")

On Error GoTo err_trap

Me.TextBox10.Value = WorksheetFunction.VLookup(Me.TextBox9, MyTableArray, 4, 0)
Me.TextBox11.Value = WorksheetFunction.VLookup(Me.TextBox9, MyTableArray, 5, 0)

err_trap:
    
    Exit Sub

However, it is not looking up the job number and date based on the batch number i enter UNLESS it has a letter in it.

For example, it wont recognise 10590, but it will recognise it if i changed it to C10590, then will display the job number and date.

Hope this makes sense!

 

[jasonb75]

See if a simple change fixes it, try using Me.TextBox9.Value in your lookups instead of just Me.TextBox9

Even though there is a number it the textbox, it is still in text format. If the match in the lookup array is in numeric format then they are not directly comparable. The textbox value needs to be converted to numeric first.

It might be the case that it needs a bit more persuasion with something like WorksheetFunction.VLookup(IIf(IsNumeric(Me.TextBox9), CLng(Me.TextBox9), Me.TextBox.9), MyTableArray
Note the intentional misspelling of IIf, it's not a typo.

 

[mageeg]

See if a simple change fixes it, try using Me.TextBox9.Value in your lookups instead of just Me.TextBox9

Even though there is a number it the textbox, it is still in text format. If the match in the lookup array is in numeric format then they are not directly comparable. The textbox value needs to be converted to numeric first.

It might be the case that it needs a bit more persuasion with something like WorksheetFunction.VLookup(IIf(IsNumeric(Me.TextBox9), CLng(Me.TextBox9), Me.TextBox.9), MyTableArray
Note the intentional misspelling of IIf, it's not a typo.

Hi Jason, Thank you so much for getting back to me.

Unfortunately neither of these methods work. All it needs is one letter anywhere in the batch number and it works fine, but without this letter it does not complete the lookup.

Seems to be very strange

 

[jasonb75]

Ok, trying a different approach, see if this works. Untested bot hopefully no typos.

Private Sub TextBox9_AfterUpdate()

Dim LookupRange As Range, rFound As Range

Set LookupRange = Sheets("Sheet2").Range("A:A")

Set rFound = LookupRange.Find(What:= Me.TextBox9.Value, LookIn:= xlValues, LookAt:= xlWhole, MatchCase:= False)

If Not rFound Is Nothing Then
    Me.TextBox10.Value = rFound.Offset(0,3).Value
    Me.TextBox11.Value = rFound.Offset(0,4).Value
Else
    Exit Sub
End If

 

[mageeg]

Ok, trying a different approach, see if this works. Untested bot hopefully no typos.

Private Sub TextBox9_AfterUpdate()

Dim LookupRange As Range, rFound As Range

Set LookupRange = Sheets("Sheet2").Range("A:A")

Set rFound = LookupRange.Find(What:= Me.TextBox9.Value, LookIn:= xlValues, LookAt:= xlWhole, MatchCase:= False)

If Not rFound Is Nothing Then
    Me.TextBox10.Value = rFound.Offset(0,3).Value
    Me.TextBox11.Value = rFound.Offset(0,4).Value
Else
    Exit Sub
End If

Thanks for your help man,
This code looks like it probably would have worked, but i fixed it simply by turning all data in the sheet to string