Forum Discussion

Debs_au's avatar
Debs_au
Copper Contributor
Aug 24, 2020
Solved

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.

  • JKPieterse's avatar
    JKPieterse
    Silver 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_au's avatar
      Debs_au
      Copper Contributor

      JKPieterse 

      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.

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Please note I only updated the case where you're looking up the ID.
      • spinaz0070's avatar
        spinaz0070
        Copper 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!

Resources