excel - VBA Match Function "Method 'Match' of Object 'WorksheetFunction' Failed" -

i'm trying use match vba function in programming project in excel first row number "1" in it. code i'm using is:

dim borradorvar integer borradorvar = worksheetfunction.match(1, sheets("usuarios").range("a1,a100"), 0) 

can explain i'm doing wrong? thanks!

you should refer range as

range("a1:a100") 

instead of

range("a1,a100") 

using comma refer a1 , a100 only.

also, if not sure if there match or not, can use application.match , store result in variant variable instead. difference application.match returns error when fails, , can check if has failed or not.

example (check msdn full code):

dim var variant  var = application.match(cells(irow, 1).value, worksheets(isheet).columns(1), 0)  if not iserror(var)     debug.print "there match" else     debug.print "no match found" end if 

a word of warning: if match after row 32,767 your code not run, because exceeds range of integer. suggest using long instead of integer. using long better practice in vba anyway.

finally, note if changes name of worksheet, code not run. it safer refer worksheet using sheet id instead of name (disclaimer: have written accepted response question).