Sleeper: Macro to select next result that an xlookup would return

[dylan1023]

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:


30734


This is fine, but my customers that I am looking up have multiple records with the same phone number, 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.

[p45cal]

The following is air code. I would need a (sanitised) copy of your workbook with userform in to go further:
X = Application.Filter(Worksheets("TELEDATA").Range("B:AH"), Worksheets("TELEDATA").Range("E:E") = Val(SalesForm.BHSDMAINNUMBERLF.Value))
Z = 2 'here you would assign a value to z (1 for the first, 2 for the 2nd etc.) from the spin button on the form
With SalesForm
.BHSDCAMPTD.Value = Index(X, Z, 1)
.BHSDPHONENUMBERTD.Value = Index(X, Z, 4)
.BHSDEMPLOYEETD.Value = Index(X, Z, 5)
.BHSDCONTACTDATETD.Value = Index(X, Z, 7)
.BHSDSALEAMOUNTTD.Value = Index(X, Z, 9)
.BHSDCUSTOMERNAMETD.Value = Index(X, Z, 11)
.BHSDADDRESSTD.Value = Index(X, Z, 12)
.BHSDCSZTD.Value = Index(X, Z, 13)
.BHSDCOMPANYNAMETD.Value = Index(X, Z, 14)
.BHSDPMTDATETD.Value = Index(X, Z, 17)
.BHSDPMTAMTTD.Value = Index(X, Z, 18)
.BHSDADMINNOTESTD.Value = Index(X, Z, 20)
.BHSDSALESORIGINTD.Value = Index(X, Z, 22)
.BHSDTSRNOTESTD.Value = Index(X, Z, 29)
.BHSDEMAILRECEIPTTD.Value = Index(X, Z, 31)
.BHSDEMAILTD.Value = Index(X, Z, 33)
End With

[p45cal]

Oh groan, cross-posted without providing links:
https://stackoverflow.com/questions/76048210/how-do-i-select-the-next-record-that-an-xlookup-function-would-return
https://www.mrexcel.com/board/threads/i-need-to-click-a-next-button-that-will-select-the-next-value-an-xlookup-would-return.1235250/
https://www.excelforum.com/excel-programming-vba-macros/1403891-macro-to-select-next-result-that-an-xlookup-would-return.html

any more?

[Aussiebear]

Yes P45cal..... it is very disappointing to see someone who clearly doesn't value to efforts of those members of forums who volunteer their time to help others. No doubt Dylan1023 will also offer the excuse that they "didn't know"..., but I'm getting very tired of this, as it's in the rules for all the forums, for all to read.

@dylan1023. Here's what I propose to do. I will visit these other forums and if I see that you have indicated on them that you apologise for cross posting, ( as I'm sure you will do here as well), then I am prepared to let this matter pass over. However if you haven't done so within the next 12 hours, then the only option left is to bid you farewell. The choice is yours, as to what happens to your fate.

[Aussiebear]

Well, it seems that the unfortunate dylan1023, has decided to remove himself from the VBAX community. Stackoverflow has deleted the thread there, MrExcel has closed the thread there, and Excelforum is yet determine an action. Cross posting has an element of selfishness and when its been indicated to others by someone other than the creator of the thread, surely it doesn't take that much courage to front up and post the other link/s.