excel vba - VBA: How to find search value from Sheet "DMR" and then from found search value row copy cell at column A and cell at Column D into Sheet "Search" -

this first time asking on vba programming sites. new vba programming (had experience 10 years ago) , trying create document cross reference tool work in user can search document number , see document number referenced in other documents. using excel 2010.

over past 3 days scouring websites, , reading excel vba programming dummies (me) coworker loaned me, code have written, comes desired inquiry box, can not seem search inquiry work, or copy paste commands work.

i trying utmost respectful of site's rules, , demonstrate efforts @ trying write code without getting else work, need help:

private sub commandbutton1_click() dim rngcell range dim ws worksheet dim lnglstrow long dim lnglstcol long dim strsearch string dim r long dim x variant  strsearch = inputbox("please enter 5 digit document number search (e.g. 00002):", "search value")  sheets("dmr").select 'loop through sheet dmr , search "search value". search value may in several rows, appear once in row. r = 1 endrow x = range("g3:ep7002").value 'yes-there 7002 rows of data starting @ column g , potentially ending @ column ep. there many blank cells. if cells(r, x).value = "search value"  'copy cells @ column , d of found search value row in sheet "dmr" range(cells(r, "a"), cells(r, "d")).select selection.copy  'switch sheet "search" & paste 2 cells sheet "dmr" sheet "search" cells a5:b5 sheets("search").select range(r, "a5:b5").select activesheet.paste  'next time find match in sheet "dmr", pasted in next row on sheet "search" pasterowindex = pasterowindex + 1  'switch sheet dmr & continue search criteria sheets("dmr").select     end if next r end sub 

if there else can provide, or way of conveying information trying acquire more clearly, please don't hesitate ask!

thank-you patience!

veronica

this searches desired range (g3:ep7002) in loop find instances , drop in sheet(search) starting @ a5:b5. lacks error checking of user3578951 leave figure out ^_^

private sub commandbutton1_click()  dim dmr worksheet dim strsearch string dim f variant dim faddress string dim frow long dim cella variant dim cellb variant  set dmr = worksheets("dmr") pasterowindex = 5 strsearch = inputbox("please enter 5 digit document number search (e.g. 00002):", "search value")  dmr.range("g3:ep7002")     set f = .find(strsearch, lookin:=xlvalues)     if not f nothing         faddress = f.address                     frow = f.row             cella = dmr.cells(frow, 1).value             celld = dmr.cells(frow, 4).value             sheets("search").cells(pasterowindex, 1) = cella             sheets("search").cells(pasterowindex, 2) = celld             pasterowindex = pasterowindex + 1             set f = .findnext(f)         loop while not f nothing , f.address <> faddress     end if end  end sub