Help me to use VLookup with ListBox value

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
431
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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??

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Check your variables and references while stepping through. Perhaps LRow = 0?
Why declare and set Rng when you don't use it?
 
Upvote 0
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...

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

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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
Back
Top