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
Comments
Post a Comment