Vlookup not importing values

[ipbr21054]

I have a userform in which the listbox populates with part numbers.
When the user selects a part number this form then closes & another opens.
The listbox part number that was selected is now in the textbox called MyPartNumber.
All the other Textboxes now take notice of the value in MyPartNumber & populate there own Textbox.

This is where it fails.
When the value is placed in the textbox MypartNumber i see a message Method Range Of Object Failed RTE1004

I have a database on sheet INFO where at column Y is the value for MyPartNumber then to the right are the values that should be populating the other textboxes.

I dont see how the part numbers get into the form.

Code shown here & fails on first line

Private Sub MyPartNumber_AfterUpdate()
'Set the background to grey (in case its currently red due to an incorrect length part number having been entered)
Me.MyPartNumber.BackColor = RGB(180, 180, 180)
If MyPartNumber.Text = "" Then Exit Sub

'Add - characters if 11 part number is 11 characters long
If Len(Me.MyPartNumber.Value) = 11 Then Me.MyPartNumber.Value = Left(Me.MyPartNumber.Value, 5) & "-" & Mid(Me.MyPartNumber.Value, 6, 3) & "-" & Right(Me.MyPartNumber.Value, 3)

'Turn background red if part number is wrong length
If Len(Me.MyPartNumber.Value) <> 13 Then
Me.MyPartNumber.BackColor = RGB(255, 0, 0)
Me.MyPartNumber.SetFocus
Exit Sub
End If

'Check to see if value exists
If WorksheetFunction.CountIf(Sheet8.Range("Y:Y"), Me.MyPartNumber.Value) = 0 Then
PartNumberMessage.Show
Me.MyPartNumber.Value = ""
Me.MyPartNumber.SetFocus
Exit Sub
End If

'Lookup values based on first control
With Me
.HondaPartNumber = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 2, 0)
.NumbersOnCase = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 3, 0)
.NumbersOnPcb = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 4, 0)
.Buttons = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 5, 0)
.GoldSwitchesOnPcb = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 6, 0)
.ItemType = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 7, 0)
.Notes = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 8, 0)
.Upgrade = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 9, 0)
.MyPrice = Format(Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 10, 0), "£#,##0.00")
End With
End Su

 

[Anthony47]

In Sheet8.Range("HONDAORIGINALNUMBERS") are "partnumbers" stored as "Numbers" or "Text"? I.e., if you format that column as "number with 2 decimal" will they be shown (a) with 2 decimals or (b) will they still be displayed without decimals?
If "(b)" then we have to do some other investigations; if it is "(a)" then try modifying the vlookup as follows:

.HondaPartNumber = Application.WorksheetFunction.VLookup(CLng(Me.MyPartNumber), Sheet8.Range("HONDAORIGINALNUMBERS"), 2, 0)

If that works, then be aware that my suggestion is that you forget abount the many Application.WorksheetFunction.VLookup and turn using one Application.Match, using the below approach:

Dim pNum As Variant

'Lookup values based on first control
pNum = Application.Match(CLng(Me.MyPartNumber), Application.WorksheetFunction.Index(Sheet8.Range("HONDAORIGINALNUMBERS"), 0, 1), False)
If Not IsError(pNum) Then
    With Me
        .HondaPartNumber = Sheet8.Range("HONDAORIGINALNUMBERS").Cells(pNum, 2)
        .NumbersOnCase = Sheet8.Range("HONDAORIGINALNUMBERS").Cells(pNum, 3)
        'etc
        'etc
    End With
End If