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
Post a Comment