vba - Application.match not working with merged cells -

i have code finds column , go last row value , display it. problem application.match not working on merged cells. here's code;

dim rng range dim lastrow long  activesheet      set rng = sheets("compas").range("a10:" & .range("zz9").end(xltoright).address)     col = application.match("*crt.*", rng, 0)      if iserror(col)          sheets("macro template").cells(2, 2) = 0      else          col = application.match("*crt. accrual*", rng, 0)         lastrow = sheets("compas").cells(1000000, col).end(xlup).row         sheets("macro template").cells(2, 2) = sheets("compas").cells(lastrow, col)      end if  end 

what i'm trying find "crt. accrual". unlike "allotment - total" on row 9 , 10(merged) "crt. accrual" on row 10. not merged row 9.

any response appreciated.

a few issues code:

1. this syntax doesn't give you'd expect

   set rng = sheets("compas").range("a10:" & .range("zz9").end(xltoright).address)

   you use this

   set rng = sheets("compas").range("a9:zz10")

2. match can search 1 array (one row or column), you'll have use match each row

   • to search on multiple rows use find

3. "col" needs declared variant (not sure how have declared)

4. you need check errors every time perform match (in else block well)

notes:

• if merge cell a1 a2, , place "test" in merged cells, "test value found in row 1 (a1), , a2 empty

this (untested) code shows how can use match on multiple rows:

option explicit  sub subname()     const hdr1 string = "*crt.*"     const hdr2 string = "*crt. accrual*"      dim row1 range, row2 range     dim col1 variant, col2 variant     dim lastrow long, macrocell range      worksheets("compas").usedrange         set row1 = .rows(9)         set row2 = .rows(10)     end     application         col1 = .match(hdr1, row1, 0)         col2 = .match(hdr2, row1, 0)         if iserror(col1) col1 = .match(hdr1, row2, 0)         if iserror(col2) col2 = .match(hdr2, row2, 0)     end      set macrocell = worksheets("macro template").cells(2, 2)      if iserror(col1) or iserror(col2)         macrocell.value2 = 0     else         worksheets("compas")             lastrow = .cells(.usedrange.rows.count + 1, col2).end(xlup).row             macrocell.value2 = .cells(lastrow, col2).value2         end     end if end sub