vba - Unable to find how to code: If Cell Value Equals Any of the Values in a Range -

i have found following code after lot of research , little of want except don't know how specify criteria reference range of cells instead of 1 single criteria.

i trying copy records , append them end of rows of matching records in sheet1. code copies records sheet3 aren't pasted corresponding rows in sheet1 want.

sub copytosheet()   dim srng range, cell range  dim drng range  set srng = sheets("sheet2").range([a2], [a65536].end(xlup))  each cell in srng  if cell.value = "80560"  set drng = sheets("sheet3").[a65536].end(xlup)(2, 1)  cell.entirerow.copy drng  end if  next  end sub 

so, there 10,000+ records in sheet2, , 30+ records in sheet1.
sheet2 , sheet1 have id number in column a.
of records in sheet1 have matching record in sheet2.
want copy records sheet2 , append them @ end of record same id in sheet1.
code above doesn't solve problem because finds 1 record "80560" , copies sheet 3.
thank in advance assistance can offer :)

-lindsay

you'll need bit of programming work on set of values , not '80560'. need done in 2 stages.

make array holds strings, may fetched location on spreadsheet. function needs made verifies whether string exists in list or not:

dim dictionaryarray() string redim dictionaryarray(1 1000) ' fill stuff need check against ' e.g. dictionaryarray(1) = '80536', etc. ' note highly inefficient, need use nice binary search algo make fast, after sorting internally  ' build function check if given string in dictionary function checkiffound(stringtocheck string, dictionaryarray() string) boolean     'implement search function here end function 

and in code posted, replace verification step

if checkiffound(cell.value, dictionaryarray) = true ' ---- implement rest of code 

---

edit: regard problem described below, can done:

sub copyfrom2to1()     dim source range, destination range     dim long, j long      set source = worksheets("sheet1").range("a1")     set dest = worksheets("sheet2").range("a2")      = 1 100          j = 1 100             if dest.cells(j,1) = source.cells(i,1)                     source.range("a" & j).range("a1:z1").copy ' a1:z1 relative a5 e.g.                     dest.range("a"&i).paste                     exit             end if         next j     next end sub 

again, highly inefficient, o(n^2) complexity, work.