excel - Search for a value in multiple columns and copy the entire row if found -

excel - Search for a value in multiple columns and copy the entire row if found -

my master worksheet (sheet 4) looks this.there 22 columns , 940 file names in col , col j.below illustration :)

col col b col c col d col e value value value value phhg.hgbranm.branch.ksds phhg.hgdwagt.ext.esds value value value value

in (sheet 3) have 184 file names

col phhg.hgbranm.branch.ksds phhg.hgdwagt.ext.esds , on....

now status files in sheet 3 either nowadays there in col or col e.i want search each file name sheet 3 in col , col e (sheet4) , if found re-create entire row , paste sheet 5.now did this

sub search_files() dim mycell, rng range, integer dim rowcount1, rowcount2, j integer , k integer myfile string k = 1 = 2 186 myfile = sheet3.cells(i, 1) j = 1 920 if (myfile = sheet4.cells(j, 5)) or (myfile = sheet4.cells(j, 1)) sheet5.cells(k, 1) = sheet4.cells(j, 1) sheet5.cells(k, 2) = sheet4.cells(j, 2) sheet5.cells(k, 3) = sheet4.cells(j, 3) sheet5.cells(k, 4) = sheet4.cells(j, 4) sheet5.cells(k, 5) = sheet4.cells(j, 5) sheet5.cells(k, 6) = sheet4.cells(j, 6) sheet5.cells(k, 7) = sheet4.cells(j, 7) sheet5.cells(k, 8) = sheet4.cells(j, 8) sheet5.cells(k, 9) = sheet4.cells(j, 9) sheet5.cells(k, 10) = sheet4.cells(j, 10) sheet5.cells(k, 11) = sheet4.cells(j, 11) sheet5.cells(k, 12) = sheet4.cells(j, 12) sheet5.cells(k, 13) = sheet4.cells(j, 13) sheet5.cells(k, 14) = sheet4.cells(j, 14) sheet5.cells(k, 15) = sheet4.cells(j, 15) sheet5.cells(k, 16) = sheet4.cells(j, 16) sheet5.cells(k, 17) = sheet4.cells(j, 17) sheet5.cells(k, 18) = sheet4.cells(j, 18) sheet5.cells(k, 19) = sheet4.cells(j, 19) sheet5.cells(k, 20) = sheet4.cells(j, 20) sheet5.cells(k, 21) = sheet4.cells(j, 21) sheet5.cells(k, 22) = sheet4.cells(j, 22) k = k + 1 end if next next end sub

its giving me results copying every file sheet 4 , copying sheet 5.i have 184 files in sheet3 sheet5 should contain 184 files getting 1107 files.why??????

i had create adjustments code run. when did, though, copied matches:

sub search_files() dim mycell, rng range, integer dim rowcount1, rowcount2, j integer, k integer dim myfile string k = 1 = 2 20 myfile = sheets("sheet3").cells(i, 1).value j = 1 20 if (myfile = sheets("sheet4").cells(j, 5).value) or (myfile = sheets("sheet4").cells(j, 1).value) sheets("sheet5").cells(k, 1) = sheets("sheet4").cells(j, 1) sheets("sheet5").cells(k, 2) = sheets("sheet4").cells(j, 2) sheets("sheet5").cells(k, 3) = sheets("sheet4").cells(j, 3) sheets("sheet5").cells(k, 4) = sheets("sheet4").cells(j, 4) sheets("sheet5").cells(k, 5) = sheets("sheet4").cells(j, 5) sheets("sheet5").cells(k, 6) = sheets("sheet4").cells(j, 6) sheets("sheet5").cells(k, 7) = sheets("sheet4").cells(j, 7) sheets("sheet5").cells(k, 8) = sheets("sheet4").cells(j, 8) sheets("sheet5").cells(k, 9) = sheets("sheet4").cells(j, 9) sheets("sheet5").cells(k, 10) = sheets("sheet4").cells(j, 10) k = k + 1 end if next next end sub

sheet 3:

sheet 4:

sheet 5:

though, if goal re-create entire row, might simpler substitute:

sheets("sheet4").rows(j).copy destination:=sheets("sheet5").range("a" & k)

in place of section of lines like:

sheets("sheet5").cells(k, 1) = sheets("sheet4").cells(j, 1)

excel vba excel-vba excel-2010