Aug 24 2020 09:27 PM
Aug 24 2020 09:27 PM
I have a spreadsheet containing columns of data from A to V, where each row is a Member record.
Is it possible to use nested Xlookup in VBA to find a record based on 3 string fields, for example:
Given_Name (col F), Surname (col G), and Street address (col J)? And if you can, how do you manage to pull the whole record from the sheet into a Variant array "GetMemberData" ?
I have tried to following code, thinking that the returned record (A:V) from the first lookup would be overwritten by the second and third; but it hasn't worked, and I'm more confused than when I started.
Would appreciate some help here. Kind thanks in advance.
' Previous "If-then-else" tests and xlookups work for the individual unique values (Member-number or Phone_number) work fine, but not this sectioin which are also entered into text boxes on the same user form, where the Member number or phone number are unknown. ' ElseIf I_Given <> "" And I_Surname <> "" And I_Street <> "" Then GetMemberData = (WorksheetFunction.XLookup(I_Given, Range("F:F"), Range("A:V")).Value) & (WorksheetFunction.XLookup(I_Surname, Range("G:G"), Range("A:V")).Value) & (WorksheetFunction.XLookup(I_Street, Range("J:J"), Range("A:V")).Value) End If
( Note: This question is in relation to the same Membership spreadsheet and "UpdateMemberForm" as in a previous subject "Run-time error 1004 : Unable to get the XLookup property of the Worksheet function class")
Aug 25 2020 03:36 AMSolution
@Debs_au Rather than using a function I would use the Autofilter object:
Private Sub Btn_Find_Member_Click() SetSwitch = True Dim GetMemberData As Variant Do Until SetSwitch = False If Len(I_Mem_Num.Value) > 0 Then 'existing code ElseIf Len(I_Phone.Value) > 0 Then 'existing code ElseIf IsEmpty(I_Given) = False And IsEmpty(I_Surname) = False And IsEmpty(I_Street) = False Then 'Clear filter Sheet1.Range("A4:V307").AutoFilter 'Now filter on entries Sheet1.Range("A4:V307").AutoFilter 6, I_Given Sheet1.Range("A4:V307").AutoFilter 7, I_Surname Sheet1.Range("A4:V307").AutoFilter 9, I_Street With Sheet1.Range("A4:V307").Offset(1).SpecialCells(xlCellTypeVisible) If .Rows.Count = 1 Then GetMemberData = .Value ElseIf .Rows.Count > 1 Then MsgBox "Found more than one match!" Exit Sub Else MsgBox "No match found!" Exit Sub End If End With SetSwitch = False Exit Do Else SetSwitch = True MsgBox "There is not enough unique information to search. Please use [Member number] OR [Phone number] OR [Given-Name and Surname and Street]" End If Loop ' >>>>> Make Text Boxes Visible <<<<< I_Suburb.Visible = True I_Postcode.Visible = True I_Birth.Visible = True I_Age.Visible = True I_Comment.Visible = True I_Email.Visible = True I_Mem_Status.Visible = True I_Mem_Receipt.Visible = True I_Mem_Date_Paid.Visible = True I_Joining_Date.Visible = True I_Film_Fee.Visible = True I_Film_Receipt.Visible = True I_Film_Date_Paid.Visible = True Mem_Category.Visible = True Btn_Gender_Male.Visible = True Btn_Gender_Female.Visible = True Btn_Gender_Other.Visible = True Btn_Vote_Yes.Visible = True Btn_Vote_No.Visible = True Btn_Photo_Yes.Visible = True Btn_Photo_No.Visible = True ' ' >>>>> Put Retrieved Data into Text Boxes <<<<<< I_Mem_Num.Value = GetMemberData(1, 1) I_Mem_Status.Value = GetMemberData(1, 2) I_Mem_Receipt.Value = GetMemberData(1, 3) I_Mem_Date_Paid.Value = GetMemberData(1, 4) Mem_Category.Value = GetMemberData(1, 5) I_Given.Value = GetMemberData(1, 6) I_Surname.Value = GetMemberData(1, 7) I_Phone.Value = GetMemberData(1, 8) I_Email.Value = GetMemberData(1, 9) I_Street.Value = GetMemberData(1, 10) I_Suburb.Value = GetMemberData(1, 11) I_Postcode.Value = GetMemberData(1, 12) If GetMemberData(1, 13) = "Male" Then Btn_Gender_Male.Value = True ElseIf GetMemberData(1, 13) = "Female" Then Btn_Gender_Female.Value = True Else: Btn_Gender_Other.Value = True End If I_Birth.Value = GetMemberData(1, 14) I_Age.Value = GetMemberData(1, 15) I_Joining_Date.Value = GetMemberData(1, 16) If GetMemberData(1, 17) = "Yes" Then Btn_Vote_Yes.Value = True Else: Btn_Vote_No.Value = True End If If GetMemberData(1, 18) = "Yes" Then Btn_Photo_Yes.Value = True Else: Btn_Photo_No.Value = True End If ' Column S (19) >Special Needs = not used I_Comment.Value = GetMemberData(1, 20) I_Film_Fee.Value = GetMemberData(1, 21) I_Film_Receipt.Value = GetMemberData(1, 22) ' I_Film_Date_Paid.Value = GetMemberData(1, 23) End Sub
Aug 25 2020 08:48 PM
Hi Jan, thank you for your suggested code here. Unfortunately, it's not a solution that will suit the end application in this instance as end-users of the forms wont have access to the spreadsheet itself. But I'm sure it will be useful for many others; and as I have only been programming for a few weeks, I have enjoyed the learning experience.
Aug 31 2020 05:59 AM
Hi All, I have solved my problem with XLookup looking up more than 1 string (ie look for both a given name and a surname, and return the record.
If the given name is in cell A1, and the surname is in cell B1.
The column to lookup to find the given name is Column F
The column to lookup to find the surname name is Column G
And then return the whole record on the row (columns A to V) if there is a match
With an error message "Record Not Found" if a match cant be found.
=XLOOKUP(A1&B1,F:F&G:G,A:V,"Record Not Found")
I hope this helps others with a similar problem.