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:

enter image description here

and bd sheet:

enter image description here

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:

enter image description here

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:

enter image description here

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 

Comments

Popular posts from this blog

networking - Vagrant-provisioned VirtualBox VM is not reachable from Ubuntu host -

c# - ASP.NET Core - There is already an object named 'AspNetRoles' in the database -

ruby on rails - ArgumentError: Missing host to link to! Please provide the :host parameter, set default_url_options[:host], or set :only_path to true -