Excel VBA: how to solve Index and Match function type mismatch error -

i encounter error in index , match functions when counter of date changes. wrote comment when face error. uploaded sample of data if needed. sample : http://s000.tinyupload.com/?file_id=00243748825638974221

here code :

sub regionalaverage()  application.screenupdating = false application.displaystatusbar = false application.enableevents = false activesheet.displaypagebreaks = false  ' *** change declaration here *** dim aname() string dim rw variant dim col variant dim date_ini date dim date_fin date  'create worksheet  ' *** add redim here, index of array start 1 *** redim aname(1 2)  date_ini = #1/1/2008# date_fin = #1/2/2008# j = 1 3     conteo = date_ini date_fin         = 1 2             sheets(i)                application                      col = .match(j, worksheets(i).range("f2:f23393"), 0)                     rw = .match(clng(conteo),     worksheets(i).range("d2:d23393"), 0)                    'error appeas here                     aname(i) = .index(worksheets(i).range("h2:h23393"), col, rw)                     end               end         next      '    computation         area = 6.429571         sheets("output").activate         range("a1").select         activecell.offset(0, j).select         colname = split(activecell(1).address(1, 0), "$")(0)         columns("" & colname & ":" & colname & "").select         selection.find(what:="", after:=activecell, lookin:=xlformulas, _          lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _          matchcase:=false, searchformat:=false).select          activecell.value = "=(sum(" & aname(1) & "," & aname(2) & "))/" & area &  ""      next conteo next j  end sub 

when date changes 1/2/2008 face error, how can solve ?!

thank you

since you're using application.match , variant data type, error not raise during call .match, variables col , rw contain error type, if value not found in search range/array.

this error value cause typemismatch error when attempting assign string array, aname().

so, you're halfway there, need error handling:

col = .match(j, worksheets(i).range("f2:f23393"), 0) rw = .match(clng(conteo), worksheets(i).range("d2:d23393"), 0)  if not iserror(col) , not iserror(rw)     aname(i) = .index(worksheets(i).range("h2:h23393"), col, rw) else     ' else if there error  end if 

alternatively, dim aname() variant, need error handling further/elsewhere in code, handle error values you're putting in array.

i observe index seems source of error, and totally not needed here, because:

index(range_object, row_num, col_num) literally same range_object.cells(row_num, col_num)

so instead, did:

aname(i) = cstr(worksheets(i).range("h2:h23393").cells(rw, col).value) 

note: assumed have rw , col in wrong position index function, , rw row number , col column number.