Excel VBA - errors with application.worksheetfunction.match -

i'm losing mind appreciate taking time help!

i suspect problems stem incorrect variable declaration haven't been able work out.

so why test procedure work:

sub testmatch3()      dim arr()           variant     dim num             long     dim searchval       variant     dim               long      redim arr(1 10)      = 1 10         arr(i) =     next      searchval = 4     debug.print getmatch(searchval, arr)  end sub  function getmatch(valuetomatch variant, matcharr variant) long      getmatch = application.worksheetfunction.match(valuetomatch, matcharr, 0)  end function 

but following gives me mismatch error (type 13):

sub newprocedure()      dim envarr              variant     dim stagerange          range     dim cell                range     dim lastrow             long     dim                   long     dim connsheet           worksheet     dim tempstring          variant     dim arr()               variant      set connsheet = thisworkbook.sheets("l1 forces")      ' find last used row in sheet , define required ranges     lastrow = connsheet.range("a11").end(xldown).row     set stagerange = connsheet.range("h11:h" & lastrow)      ' have big table of data in "env sheet" transfer large 2d array     envarr = thisworkbook.worksheets("env").range("a6").currentregion      ' envarray, make second column new 1d array     ' new array has upper bound dimension equal number of rows in envarr     redim arr(lbound(envarr, 1) ubound(envarr, 1))     = lbound(arr) ubound(arr)         arr(i) = envarr(i, 2)     next      tempstring = "1140"     debug.print getmatch(tempstring, arr)  end sub  function getmatch(valuetomatch variant, matcharr variant) long      getmatch = application.worksheetfunction.match(valuetomatch, matcharr, 0)  end function 

just note value "1140" exists in arr!

thanks

i suppose in sheet number 1140 , try match string "1140". did try write

tempstring = 1140 

without quotes?

alternatively: make sure there string in excel sheet: ="1140" , not formatted string. return value of =type(cell) ('cell' containing 1140) has 2.