Forum Discussion
Debs_au
Aug 24, 2020Copper Contributor
Runtime error 1004: Unable to get the Xlookup property of the worksheetfunction class
I have the current MS Office 365, and am using Excel.
I created a button on Sheet1 to click [Change Member Details]
On click, the fields on the form are initialized, with only those to use for the search being made visible on the "UpdateMemberForm".
The user form prompts for required search input (member number, or a phone number, or a combination of name and street address).
I entered the member number, and clicked the [Find Member] button on the "UpdateMemberForm".
The "WorksheetFunction.XLookup(I_Mem_Num, "A:A", "A:V")" was to search Sheet1, column A, and return the string of data to an array "GetMemberData" that I wanted to subsequently display in all the fields on the user form.
The data was to be simply read, or allow for record change/correction and written back to sheet1, or deleted if [Delete Member] button was clicked.
The member numbers are in Sheet1 Column A.
Each record row is contained within columns A through V.
As a test, I used a member number that I knew was in Sheet1 column A.
This is the code I am using that returns the error: Runtime error 1004: Unable to get the Xlookup property of the worksheetfunction class, highlighting the XLookup code line.
' >>>>> UPDATE MEMBER <<<<<
'
Sub RectangleRoundedCorners3_Click()
Set Sheet_Name = ThisWorkbook.Sheets("Sheet1")
Dim Array_Count As Long
Dim SetSwitch As Boolean
Dim GetMemberData As Variant
ReDim GetMemberData(0 To 22) As Variant
Load UpdateMemberForm
UpdateMemberForm.Show
End Sub
__________________________________________________
'
' >>>>> Begin User Form Data Input <<<<<
' ==========================
Private Sub I_Mem_Num_Enter()
I_Mem_Num.BackColor = vbYellow
I_Suburb.Visible = False
I_Postcode.Visible = False
I_Birth.Visible = False
I_Age.Visible = False
I_Comment.Visible = False
I_Email.Visible = False
I_Mem_Status.Visible = False
I_Mem_Receipt.Visible = False
I_Mem_Date_Paid.Visible = False
I_Joining_Date.Visible = False
I_Film_Fee.Visible = False
I_Film_Receipt.Visible = False
I_Film_Date_Paid.Visible = False
Mem_Category.Visible = False
Btn_Gender_Male.Visible = False
Btn_Gender_Female.Visible = False
Btn_Gender_Other.Visible = False
Btn_Vote_Yes.Visible = False
Btn_Vote_No.Visible = False
Btn_Photo_Yes.Visible = False
Btn_Photo_No.Visible = False
End Sub
Private Sub I_Given_Enter()
I_Mem_Num.BackColor = vbWhite
I_Given.BackColor = vbYellow
End Sub
Private Sub I_Surname_Enter()
I_Given.BackColor = vbWhite
I_Surname.BackColor = vbYellow
End Sub
Private Sub I_Phone_Enter()
I_Surname.BackColor = vbWhite
I_Phone.BackColor = vbYellow
End Sub
Private Sub I_Phone_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Stop_Switch = "Go"
Do Until Stop_Switch = "Stop"
If IsNumeric(I_Phone.Text) = False Then
I_Phone.Text = InputBox("Please enter 8 or 10 digit phone number ")
ElseIf IsNumeric(I_Phone.Text) And Len(I_Phone.Text) = 8 Then
I_Phone.Text = Format(I_Phone.Text, "0000-0000")
Stop_Switch = "Stop"
Exit Do
ElseIf IsNumeric(I_Phone.Text) And Len(I_Phone.Text) = 10 Then
I_Phone.Text = Format(I_Phone.Text, "0000-000-000")
Stop_Switch = "Stop"
Exit Do
Else
Stop_Switch = "Go"
End If
Loop
End Sub
Private Sub I_Street_Enter()
I_Email.BackColor = vbWhite
I_Street.BackColor = vbYellow
End Sub
Private Sub L_Mem_Num_Click()
End Sub
__________________________________________________
Private Sub Btn_Find_Member_Click()
SetSwitch = True
ReDim GetMemberData(0 To 21) As Variant
Do Until SetSwitch = False
If I_Mem_Num.Value > 0 Then
MsgBox I_Mem_Num
GetMemberData = WorksheetFunction.XLookup(I_Mem_Num, "A:A", "A:V")
MsgBox GetMemberData
SetSwitch = False
Exit Do
As a further test, I created Sheet2 using A1 as the record number input to search, with the Xlookup formula in Sheet2, A3. It read the data on sheet1, and returned the array of values from Sheet1 A to V, and worked perfectly.
=XLOOKUP(A1,Sheet1!A:A,Sheet1!A:V)
Help with a solution to this problem would be greatly appreciated.
Thank you in advance.
Debs_au If you are doing an XLookup on the ID it will fail because you are getting the value from a textbox and the data in your worksheet contains numerical ID's. This means XLookup will return #N/A, which results in a run-time error. In addition you must change your declaration of GetMemberData so XLOOKUP can add the found values to it. This appears to work:
Private Sub Btn_Find_Member_Click() SetSwitch = True Dim GetMemberData As Variant Do Until SetSwitch = False If I_Mem_Num.Value > 0 Then MsgBox I_Mem_Num GetMemberData = WorksheetFunction.XLookup(I_Mem_Num * 1, Range("A:A"), Range("A:V")).Value ' MsgBox GetMemberData SetSwitch = False Exit Do ElseIf I_Phone > 0 Then GetMemberData = WorksheetFunction.XLookup(I_Phone, "H:H", "A:V") SetSwitch = False Exit Do ElseIf IsEmpty(I_Given) = False And IsEmpty(I_Surname) = False And IsEmpty(I_Street) = False Then GetMemberData = WorksheetFunction.XLookup(I_Given & I_Surname & I_Street, "F:F" & "G:G" & "J:J", "A:W") 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
Except for the last statement as it appears only 22 columns are in the source range of the XLOOKUP.
- JKPieterseSilver Contributor
Debs_au If you are doing an XLookup on the ID it will fail because you are getting the value from a textbox and the data in your worksheet contains numerical ID's. This means XLookup will return #N/A, which results in a run-time error. In addition you must change your declaration of GetMemberData so XLOOKUP can add the found values to it. This appears to work:
Private Sub Btn_Find_Member_Click() SetSwitch = True Dim GetMemberData As Variant Do Until SetSwitch = False If I_Mem_Num.Value > 0 Then MsgBox I_Mem_Num GetMemberData = WorksheetFunction.XLookup(I_Mem_Num * 1, Range("A:A"), Range("A:V")).Value ' MsgBox GetMemberData SetSwitch = False Exit Do ElseIf I_Phone > 0 Then GetMemberData = WorksheetFunction.XLookup(I_Phone, "H:H", "A:V") SetSwitch = False Exit Do ElseIf IsEmpty(I_Given) = False And IsEmpty(I_Surname) = False And IsEmpty(I_Street) = False Then GetMemberData = WorksheetFunction.XLookup(I_Given & I_Surname & I_Street, "F:F" & "G:G" & "J:J", "A:W") 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
Except for the last statement as it appears only 22 columns are in the source range of the XLOOKUP.
- Debs_auCopper Contributor
Thank you SOOOO much! I've been stressing over this for days, and you've explained it so I understand the problem. Also, thank you for showing the solution to getting the returned values from the array into the text-boxes. So much appreciated.
- JKPieterseSilver ContributorPlease note I only updated the case where you're looking up the ID.
- spinaz0070Copper Contributor
Hi,
I have this same problem with the following formula: Set NamedRange = WorksheetFunction.XLookup(ProdItem, sht2.Range("A3:A71"), sht2.Range("B3:B71")). Since the ProdItem is a number from a UserForm textbox, how do I change this to get past the error?
Thanks!