• run time error ‘1004’ : Unable to get the match property of the Worksheetfunction class!

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » run time error ‘1004’ : Unable to get the match property of the Worksheetfunction class!

    Author
    Topic
    #508276

    Hello,

    I have this userform that I’m using to update company information. i have a button in the spreadsheet that opens up a userfom with the name of the company in C5 cell. any idea why i get this error?

    In the userfom Initialize i have this code:

    Code:
    Private Sub UserForm_Initialize()
    currentrow = Application.WorksheetFunction.Match(Sheets(“Sheet1”).Cells(5, 3), Range(“VendorList”))
    txtvendor.Text = ws.Cells(currentrow, 1).Text
    txtcontact.Text = ws.Cells(currentrow, 5).Text
    txtphone.Text = ws.Cells(currentrow, 6).Text
    txtemail.Text = ws.Cells(currentrow, 7).Text
    txtfax.Text = ws.Cells(currentrow, 8).Text
    End Sub
    
    Viewing 2 reply threads
    Author
    Replies
    • #1592033

      abouya,

      Where is ws defined? Is it defined as global?

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1592034

        Hello,

        Thanks for asking.

        here is the entire code Init code:

        Code:
        Private Sub UserForm_Initialize()
        
        
            ‘Puts the user form in the middle of the Excel screen when opened.
            Me.StartupPosition = 0
            Me.Top = (Application.Height / 2) – (Me.Height / 2)
            Me.Left = (Application.Width / 2) – (Me.Width / 2)
        
        
        Dim ws As Worksheet
        
        Set ws = Sheets(“Vendors”)
        
        If Sheets(“Sheet1”).Cells(5, 3) = “” Then  ‘C5 is where company/vendor name is
        
        MsgBox “Please select a Vendor!”, vbInformation, “Required Field!”
        
        End
        
        
        Else
        
        
        currentrow = Application.WorksheetFunction.Match(Sheets(“Sheet1”).Cells(5, 3), Range(“VendorList”))
        txtvendor.Text = ws.Cells(currentrow, 1).Text
        txtcontact.Text = ws.Cells(currentrow, 5).Text
        txtphone.Text = ws.Cells(currentrow, 6).Text
        txtemail.Text = ws.Cells(currentrow, 7).Text
        txtfax.Text = ws.Cells(currentrow, 8).Text
        
        End If
        
        End Sub
    • #1592062

      abouya,

      Again still not enough information. Don’t know what the address range for VendorList is? This is important since the Match function returns a RELATIVE position in the list…NOT a row number!

      46800-MatchRelative

      It would be most helpful if you could post your excel file (after sanitizing any sensitive information) so we can see the whole picture. This will save a lot of this back and forth.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1592094

        Hello again,

        Your definitely right, I uploaded a sample file for this matter. Please have a look at this issue if you can. I had an attempt to use match and index formula to get the current row but i have no idea how to use it even after some research.

        Thank you so much in advance.

        46803-matchProperty

    • #1592098

      Abouya,

      That’s much better.

      Here’s what I did to make it work:

      First I changed the Definition of the RangeName VendorList to:
      [noparse]=OFFSET(Vendors!$A$2,0,0,COUNTA(Vendors!$A$2:$A$1001),COUNTA(Vendors!$A$2:$A$2))[/noparse]

      Next I added 1 to the value returned by the match formula to account for the Vendors header to get a true row number.
      [noparse]currentrow = Application.WorksheetFunction.Match(Sheets(“Sheet2”).Cells(2, 1), Range(“VendorList”)) + 1[/noparse]

      I also added a RunForm macro to make it easier to test via Alt+F8.

      46804-MatchFix

      Test file: 46805-matchProperty-RGv1

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1592109

        Chapeau!

        Thank you so much Sir. Your help is appreciated.

        • #1592125

          Hi abouya

          ..if this is completed, Click your [Thanks] button to give RG the thanks he deserves.
          ..if you still have any questions, ask again

          zeddy

    Viewing 2 reply threads
    Reply To: run time error ‘1004’ : Unable to get the match property of the Worksheetfunction class!

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: