locked
Match Function not working in VBA for Excel RRS feed

  • Question

  • I Have created this macro to  get the match result for one sheet, but the macro gives me an error message " unable to get the match property of the worksheetfunction class", bearing in mind that "account" is a range name of one column in the workbook.

    Please help

    

    Sub vlookup_account()


    Dim MatchCompte As Integer
    Dim strcompte As string

    strcompte = Sheets("table1").Range("b16").Value

     MatchCompte = Application.WorksheetFunction.Match(strcompte, Range("account"), 1)
         
      
        MsgBox (MatchCompte)

        
       
    End Sub

    Monday, November 4, 2013 5:24 PM

Answers

  • Hi WBA1,

    The error “unable to get the match property of the worksheetfunction class” will pop up when the result is not matched. For a workaround, I suggest to use the code below:

    Sub vlookup_account()
     Dim MatchCompte As Integer
     Dim strcompte As String
     strcompte = Sheets("table1").Range("b16").Value
     On Error Resume Next
     MatchCompte = Application.WorksheetFunction.Match(strcompte, Range("account"), 1)
     MsgBox (MatchCompte)
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 14, 2013 3:45 AM

All replies

  • Re:  unable to match

    Add a zero...
      MatchCompte = Application.WorksheetFunction.Match(strcompte, Range("account"), 1, 0)

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 2:56 AM
    Monday, November 4, 2013 6:41 PM
  • Hi WBA1,

    The error “unable to get the match property of the worksheetfunction class” will pop up when the result is not matched. For a workaround, I suggest to use the code below:

    Sub vlookup_account()
     Dim MatchCompte As Integer
     Dim strcompte As String
     strcompte = Sheets("table1").Range("b16").Value
     On Error Resume Next
     MatchCompte = Application.WorksheetFunction.Match(strcompte, Range("account"), 1)
     MsgBox (MatchCompte)
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 14, 2013 3:45 AM
  • Try : MatchCompte = Application.Match(strcompte, Range("account"), 1) Regards. Daniel > I Have created this macro to  get the match result for one sheet, but the > macro gives me an error message " unable to get the match property of the > worksheetfunction class", bearing in mind that "account" is a range name of > one column in the workbook. > > > > Please help > > ���Sub vlookup_account() > > > Dim MatchCompte As Integer > Dim strcompte As string > > strcompte = Sheets("table1").Range("b16").Value > >  MatchCompte = Application.WorksheetFunction.Match(strcompte, > Range("account"), 1)       >    >     MsgBox (MatchCompte) > >      >     > End Sub > > > > ----- > http://social.Msdn.microsoft.com/Forums/en-US/exceldev/thread/1c0ac3f8-3319-48ef-ac53-485fcdfef4a5#1c0ac3f8-3319-48ef-ac53-485fcdfef4a5 > -----
    Thursday, November 14, 2013 11:13 AM