Match Function not working in VBA for Excel

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

Answer 1

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)

Answer 2

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.

Answer 3

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