Excel VBA - Match function error (Unable to get match property) -


i'm trying match search filter out rows data matched. example, have "buttonname" shows column filter has put , "datarange.rows(1)" range on tries search match. when editing, shows:

object defined error

and showing:

unable match property

please tell me error in code?

private sub commandbutton21_click()      dim mybutton optionbutton     dim searchstring string     dim buttonname variant     dim sht worksheet     dim myfield long     dim datarange range     dim mysearch1, mysearch2, mysearch3 variant      'load sheet variable       set sht = activesheet       set = activesheet     'unfilter data (if necessary)       on error resume next       sht.showalldata       on error goto 0      'filtered data range (include column heading cells)        set datarange = sht.range("a13:al3000") 'cell range      'retrieve user's search input      mysearch1 = sht.range("d4").text 'control form     ''contains data entered in d4cell       buttonname = sht.range("m12").text            if not iserror(worksheetfunction.match(buttonname, datarange.rows(1), 0))                   myfield = worksheetfunction.match(buttonname, datarange.rows(1), 0)      else        msgbox "no match found in range(" & rngtosearch.address & ")."      end if       'filter data        datarange.autofilter _        field:=myfield, _        criteria1:="=*" & mysearch1 & "*", _        operator:=xland, _        criteria2:="=*" & mysearch2 & "*", _        operator:=xland, _        criteria2:="=*" & mysearch3 & "*", _        operator:=xland  end sub 

the error occurs on line:

if not iserror(worksheetfunction.match(buttonname, datarange.rows(1), 0)) 

make following changes:

dim myfield variant   'important  myfield = application.match(buttonname, datarange.rows(1), 0)  if iserror(myfield)     msgbox "no match found in range(" & rngtosearch.address & ")." else     'filter data     datarange.autofilter _     field:=myfield, _     criteria1:="=*" & mysearch1 & "*", _     operator:=xland, _     criteria2:="=*" & mysearch2 & "*", _     operator:=xland, _     criteria2:="=*" & mysearch3 & "*", _     operator:=xland end if 

Comments