Results 1 to 3 of 3

Macro to select next result that an xlookup would return

Threaded View

  1. #1
    Registered User
    Join Date
    06-17-2021
    Location
    Washington, USA
    MS-Off Ver
    Current
    Posts
    19

    Macro to select next result that an xlookup would return

    I am creating a UserForm that is pulling records of my customers from a worksheet to my UserForm using this code:

    Sub employeelookup()
     SalesForm.BHSDEMPLOYEETD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("F:F"))
    End Sub
    Sub salesoriginlookup()
    SalesForm.BHSDSALESORIGINTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("W:W"))
    End Sub
    Sub contactdatelookup()
    SalesForm.BHSDCONTACTDATETD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("H:H"))
    End Sub
    Sub saleamountlookup()
    SalesForm.BHSDSALEAMOUNTTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("J:J"))
    End Sub
    Sub companynamelookup()
    SalesForm.BHSDCOMPANYNAMETD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("O:O"))
    End Sub
    Sub customernamelookup()
    SalesForm.BHSDCUSTOMERNAMETD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("L:L"))
    End Sub
    Sub addresslookup()
    SalesForm.BHSDADDRESSTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("M:M"))
    End Sub
    Sub cszlookup()
    SalesForm.BHSDCSZTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("N:N"))
    End Sub
    Sub phonelookup()
    SalesForm.BHSDPHONENUMBERTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("E:E"))
    End Sub
    Sub emaillookup()
    SalesForm.BHSDEMAILTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("AH:AH"))
    End Sub
    Sub pmtdatelookup()
    SalesForm.BHSDPMTDATETD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("R:R"))
    End Sub
    Sub pmtamtlookup()
    SalesForm.BHSDPMTAMTTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("S:S"))
    End Sub
    Sub adminnoteslookup()
    SalesForm.BHSDADMINNOTESTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("U:U"))
    End Sub
    Sub tsrnoteslookup()
    SalesForm.BHSDTSRNOTESTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("AD:AD"))
    End Sub
    Sub camplookup()
    SalesForm.BHSDCAMPTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("B:B"))
    End Sub
    Sub emailreceiptlookup()
    SalesForm.BHSDEMAILRECEIPTTD.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("TELEDATA").Range("E:E"), Worksheets("TELEDATA").Range("AF:AF"))
    End Sub
    The data is then populating this form:

    Screenshot 20SFDFSDFSE23-04-18 101459.png

    This is fine, but my customers that I am looking up have multiple records, and I want a function that selects the next record down that falls into the XLookup criteria.

    TLDR, i want to press a button that shows the next thing down a list that my xlookup would be returning.
    Last edited by Dylan1023; 04-18-2023 at 05:25 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Xlookup make result blank if contains keyword
    By LocSpreader in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-01-2023, 03:57 AM
  2. [SOLVED] trying to use xlookup to get the result of looking at 2 columns
    By gjjh25 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-30-2022, 08:19 AM
  3. [SOLVED] Query on using XLOOKUP to return a value just once
    By Majestic1976 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-09-2021, 11:33 AM
  4. Xlookup return maximum value
    By tommackie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-09-2021, 04:10 AM
  5. using xlookup to select last non-null value
    By veloluz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-28-2021, 10:40 AM
  6. Replies: 1
    Last Post: 02-12-2013, 06:02 PM
  7. Macro to select the result of an autofilter action
    By at_my_work@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2005, 07:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1