Handling formula result to Vlookup in VBA

I have a table in an Excel spreadsheet that pulls all data from external spreadsheets. I have created a form which should pull the data to the form showing all details connected to the product. The product number is typed into the ComboBox and then searched with Vlookup in the table. When I type the product number which for example looks like FC222555, after pressing the search button I get Run-Time Error 1004 Unable to get the Vlookup property of the worksheetfunction class. I found out that Vlookup cannot find this number due to the formula in the cell and I need to convert the formula result so Vlookup can find it. Please see the code:

Private Sub CommandButton1_Click()
'variables
Dim Internal_names As String
Dim Prdd As Date
Dim Vn As String
Dim Sh1 As String
Dim Ln As Long
Dim Fx As Long
Dim Pds As String
Dim Pdd As Date
Dim Iss As String
Dim Cms As String
Dim Cmd As Date
Dim Shs As String
Dim Shd As Date
Dim Com As String
Dim x As String

'combobox value vlookup
With ThisWorkbook.Worksheets("Delivery")
Set Rng = Range("A:Y")
Prdd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 21, False)
Vn = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 15, False)
Sh1 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 1, False)
Ln = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 2, False)
Fx = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 3, False)
Pds = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 4, False)
Pdd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 5, False)
Iss = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 6, False)
Cms = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 9, False)
Cmd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 10, False)
Shs = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 11, False)
Shd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 12, False)
Com = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 14, False)

'text boxes value
dateprod.Value = Prdd
vin.Value = Vn
shift.Value = Sh1
line.Value = Ln
fixture.Value = Fx
pdishift.Value = Pds
pdidate.Value = Pdd
details.Value = Iss
cmmshift.Value = Cms
cmmdate.Value = Cmd
shipshift.Value = Shs
shipdate.Value = Shd
comments.Value = Com


End With
End Sub

I was trying to get the variable to handle the formula results but then I got an invalid qualifier error.


Dim x As String

With ThisWorkbook.Worksheets("Delivery")
x = ComboBox1.Value
Prdd = Application.WorksheetFunction.VLookup(x.Value, Rng, 21, False)
Vn = Application.WorksheetFunction.VLookup(x.Value, Rng, 15, False)
Sh1 = Application.WorksheetFunction.VLookup(x.Value, Rng, 1, False)
Ln = Application.WorksheetFunction.VLookup(x.Value, Rng, 2, False)
Fx = Application.WorksheetFunction.VLookup(x.Value, Rng, 3, False)

Can you please help me understand what I need to do to make it work?

Solution

You will get that error if there's no match made on the lookup table for the value being searched.

FYI it would be much more efficient to use a single Application.Match() to locate the matched row, and then read the values directly from that row. Match() will return an error value if there's no match made, and you can test for that using IsError().

For example:

Private Sub CommandButton1_Click()
    
    Dim m As Variant, rw As Range, ws As Worksheet, v
    
    Set ws = ThisWorkbook.Worksheets("Delivery")
    
    v = ComboBox1.Value
    m = Application.Match(v, ws.Columns("A"), 0) 'try to find matched row
    
    If Not IsError(m) Then  'got a match?
        Set rw = ws.Rows(m)
        dateprod.Value = rw.Cells(21).Value
        vin.Value = rw.Cells(15).Value
        shift.Value = rw.Cells(1).Value
        Line.Value = rw.Cells(2).Value
        'etc etc
    Else
        MsgBox "No match found for '" & v & "'!", vbExclamation
    End If
   
End Sub