Help me to use VLookup with ListBox value

[Eric Penfold]

Trying to use the VLookup with Listbox Value but it keeps saying error 1004. I`ve checked all the rest of the code and it`s fine??

Sub MatchEmail()
    
    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim LRow   As Long
    Dim Rng    As Range

    Set wb = Workbooks("Personal.xlsb")
    Set ws = wb.Worksheets("Emails")
    
    Set Rng = ws.Range("A1").CurrentRegion
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Me.EmailAddress.Value = Application.WorksheetFunction.VLookup(Me.SuppliersName.Value, ws.Range("A2:B" & LRow), 2)
    
    Call Emails
    
End Sub

 

[Micron]

Check your variables and references while stepping through. Perhaps LRow = 0?
Why declare and set Rng when you don't use it?

 

[Domenic]

The error likely occurs because it did not find the supplier listed in your table. If you use the VLookup method of the Application object instead of the WorksheetFunction object, you'll get a non-breaking error, for which you can test. Also, since you've already defined your range, there's no need to find the last row and re-define it. Try the following...

Sub MatchEmail()
   
    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim rng    As Range
    Dim result As Variant

    Set wb = Workbooks("Personal.xlsb")
    Set ws = wb.Worksheets("Emails")
    Set rng = ws.Range("A1").CurrentRegion
   
    result = Application.VLookup(Me.SuppliersName.Value, rng, 2, 0) 'set the last argument to 0 for an exact match

    If Not IsError(result) Then
        Me.EmailAddress.Value = result
        Call Emails
    Else
        MsgBox "Supplier not found!", vbExclamation 'optional
    End If
   
End Sub

Hope this helps!