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

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
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:

VBA Code:
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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 6, 2021
Messages
81
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
Ok, trying a different approach, see if this works. Untested bot hopefully no typos.
VBA Code:
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

Board Regular
Joined
Jun 6, 2021
Messages
81
Ok, trying a different approach, see if this works. Untested bot hopefully no typos.
VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,259
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top