Excel VBA Find All -

as usual, i've wasted hours working on problem before giving in , asking help, appreciated!

i'm running while loop on sheet test1 of data in order copy data specific row on sheet test2 parameters match, way i'm doing looping through every row of sheet 2 - both sheets of data on 50,000 lines long, although method works 10 row tests taking several hours work full data. wondering if simplify code, ideally search matching rows rather comparing each one?

i have tried using find couldn't work multiple parameters.

this current code, i've omitted part set parameter settings save space present in worksheet:

while row1 <= lastrow1     param1 = sheets("test1").cells.range("a" & row1).value     param2 = sheets("test1").cells.range("b" & row1).value     param3 = sheets("test1").cells.range("d" & row1).value     param4 = sheets("test1").cells.range("e" & row1).value     param5 = sheets("test1").cells.range("f" & row1).value     cell_to_copy = sheets("test1").cells.range("g" & row1).value *****this bit want optimise************         row2 = 2 lastrow2             kparam1 = sheets("test2").cells.range("a" & row2).value             kparam2 = sheets("test2").cells.range("b" & row2).value             kparam3 = sheets("test2").cells.range("i" & row2).value             kparam5 = sheets("test2").cells.range("g" & row2).value                 if (instr(1, kparam1, param1) > 0) _                 , ((instr(1, kparam2, param2) > 0) or (instr(1, param2, kparam2) > 0)) _                 , ((instr(1, kparam3, param3) > 0) or (instr(1, kparam3, param4) > 0)) _                 , (instr(1, kparam5, param5) > 0) _                                     sheets("test2").cells.range("l" & row2).value = cell_to_copy                 end if         next row2 ****************************************************         row1 = row1 + 1 wend 

i'd change middle section instead perform search function locate rows match if query, , copy cell_to_copy cell l on row.

is possible? can concatenate searchable values test2 single cell if it's absolutely necessary, i'd rather not if can avoided match false positives.

thanks in advance,

joe

ok, rule seems be, if

test1.column found inside test2.column test1.column b found inside test2.column b test1.column d found inside test2.column test1.column e found inside test2.column test1.column f found inside test2.column g 

then copy test1.column g test2.column l

is right?

why can't using formula on sheet2?

maybe: test2.column l formula = =if(find(a:a,sheet1!a:a)>0,sheet1!g:g)

that formula needs or other conditions make check other columns - try after lunch!

if not, ok, here think need consider using ado , treating worksheet db table.

if show source data looks like, , result need, can try , write sample you, far best option learn fish using below links.

see here this: msdn kb: how use ado excel data visual basic or vba

and read: treat excel relational data source on excel user mvp website

and read: office space: using ado query excel spreadsheet

basically task read filtered data need ado recordset, use copyfromrecordset method dump results sheet2 in 1 step

so have reading do, let know if need example or more help

philip