Runtime error 1004: Unable to get the Xlookup property of the worksheetfunction class

[Debs_au]

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.

[#2 · Accepted solution]

@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.

[#3]

Please note I only updated the case where you're looking up the ID.

[#4]

@Jan Karel Pieterse 

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.