excel - VBA find and findnext to paste values -

i have sheet want use find , findnext search values on sheet bd , copy them main sheet plan1 if value on alocacao matches cells on column 5.

i used have 4 spaces named ranges tecnico1, tecnico2, tecnico3 , tecnico4 paste values , code works fine.

this how looks:

and bd sheet:

and code:

sub verifprod_click()     dim foundcell range, firstaddr string, fnd string, long      fnd = sheets(1).range("alocacao").value      set foundcell = sheets("bd").columns(5).find(what:=fnd, _         after:=sheets("bd").cells(rows.count, 5), lookat:=xlpart, _         lookin:=xlformulas, searchorder:=xlbyrows, searchdirection:=xlnext)      if foundcell nothing exit sub              = + 1         sheets("plan1").range("tecnico" & i).value = foundcell.offset(, -3).value         sheets("plan1").range("upps0" & i).value = foundcell.offset(, -1).value          set foundcell = sheets("bd").columns(5).findnext(after:=foundcell)     loop until foundcell.address = firstaddr or >= 4 end sub 

however, realize i'll need more fields because maybe insert more 4 tecnicos on alocacao. how looks:

and changed part of code:

if foundcell nothing exit sub                      = + 1             sheets("plan1").range("tecnico" & i).value = foundcell.offset(, -3).value             sheets("plan1").range("upps0" & i).value = foundcell.offset(, -1).value              set foundcell = sheets("bd").columns(5).findnext(after:=foundcell)         loop until foundcell.address = firstaddr or >= 10 

so expecting fills 4 fields still have 4 matches, got result:

as i'm new using find , findnext, don't know have change fill cells matches without repeating it.

any suggestions help! maybe there couldn't notice there.

i used suggestion of @luuklag , it's working.

sub verifprod_click()     dim foundcell range, firstaddr string, fnd string, long      fnd = sheets(1).range("alocacao").value      set foundcell = sheets("bd").columns(5).find(what:=fnd, _         after:=sheets("bd").cells(rows.count, 5), lookat:=xlpart, _         lookin:=xlformulas, searchorder:=xlbyrows, searchdirection:=xlnext)      if not foundcell nothing         firstaddr = foundcell.address     end if     if foundcell nothing exit sub              = + 1         sheets("plan1").range("tecnico" & i).value = foundcell.offset(, -3).value         sheets("plan1").range("upps0" & i).value = foundcell.offset(, -1).value          set foundcell = sheets("bd").columns(5).findnext(after:=foundcell)     loop until foundcell.address = firstaddr or >= 10 end sub