excel - Use 1 Columns Values as a search array -

i have code searches individual values across whole workbook. problem have need type in codes searching individually code selection contained in single column on worksheet within workbook. want search @ values in column , use them array across whole workbook.

my total code looks this

sub fmes()  dim headers() string: headers = split("fmes code,part no,part name,fm id,failure mode & cause,fmcn,ptr,etr", ",")      worksheets.add().name = "fmes"     dim wsfmes worksheet: set wsfmes = sheets("fmes")     wsfmes.move after:=worksheets(worksheets.count)     wsfmes.cells.clear      application.screenupdating = false      wsfmes         = 0 ubound(headers)             .cells(2, + 2) = headers(i)             .columns(i + 2).entirecolumn.autofit         next         .cells(1, 2) = "fmes table"         .range(.cells(1, 2), .cells(1, ubound(headers) + 2)).mergecells = true         .range(.cells(1, 2), .cells(1, ubound(headers) + 2)).horizontalalignment = xlcenter         .range(.cells(1, 2), .cells(2, ubound(headers) + 2)).font.bold = true     end      dim sourcecell range, firstadr string     dim rowcounter long: rowcounter = 3      dim searchtarget() string     searchtarget = split("tm-crit-part,tm-u-ifsd-hpt,tm-d-ifsd-hpt,tm-hpt-cbo,tm-hpt2-ngv-burn-min,tm-hpt2-ngv-maj,tm-hpt-intgrty-loss,oil-fz2-minor leak,oil-fz2-major leak,fire-oil-zn2", ",")      = 0 ubound(searchtarget)         if worksheets.count > 1             j = 1 worksheets.count - 1             sheets(j)                 set sourcecell = .columns(8).find(searchtarget(i), lookat:=xlpart, lookin:=xlvalues)                 if not sourcecell nothing                     firstadr = sourcecell.address                                             wsfmes.cells(rowcounter, 2).value = searchtarget(i)                         wsfmes.cells(rowcounter, 3).value = .cells(3, 10)                         wsfmes.cells(rowcounter, 4).value = .cells(2, 10)                         wsfmes.cells(rowcounter, 5).value = .cells(sourcecell.row, 2).value                         k = 0 sourcecell.row - 1                             if .cells(sourcecell.row - k, 3).value <> "continued."                                 wsfmes.cells(rowcounter, 6).value = .cells(sourcecell.row - k, 3).value                                 exit                             end if                         next k                         wsfmes.cells(rowcounter, 7).value = .cells(sourcecell.row, 14).value                           set sourcecell = .columns(8).findnext(sourcecell)                         rowcounter = rowcounter + 1                     loop while not sourcecell nothing , sourcecell.address <> firstadr                 end if             end             next j         end if     next       end sub 

the section defines search criteria

  dim searchtarget() string     searchtarget = split("tm-crit-part,tm-u-ifsd-hpt,tm-d-ifsd-hpt,tm-hpt-cbo,tm-hpt2-ngv-burn-min,tm-hpt2-ngv-maj,tm-hpt-intgrty-loss,oil-fz2-minor leak,oil-fz2-major leak,fire-oil-zn2", ",")      = 0 ubound(searchtarget)         if worksheets.count > 1             j = 1 worksheets.count - 1             sheets(j)                 set sourcecell = .columns(8).find(searchtarget(i),  

the codes searching tm-crit-part...etc problem there hundreds of these , contained in worksheet called fmes codes between cells a4 , a397. there way can define values in range of cells search criteria ?

you use simple solution when passing range variable not object variable two-dimensional array. there changes required in code. follow code:

dim searchtarget variant         '<<change type, remove brackets     searchtarget = sheets("fmes").range("a4:a297")  '<<define array             'our array 2 dimensional starting (1,1)      = 1 ubound(searchtarget, 1)    '<<change starting point , loop scope         '...some of code here         set sourcecell = .columns(8).find(searchtarget(i, 1),   '<<changes here 

hint! possible convert two-dimensional array coming single colum range one-dimensional array. if required use transpose function.