Excel VBA with single search criteria, loop for all distinct values -

i getting error message when run macro:

run-time error '6': overflow

i have 2 worksheets; search , data. 'data' worksheet contains 2 columns, column numbers want search through , column b alphanumeric value want copy , paste 'search' worksheet when number match found. because number searching can listed unknown number of times want macro loop through find of instances, copy value immediate right , paste 'search' worksheet in cell d3 , going down row multiple instances of number being found.

the number searching found in cell b3 on 'search' worksheet.

this sample of 'data' worksheet looks like:

id          iss_id 108143      136kqv4 108143      173hbk3 108143      136kqx0 109728      7805jm1 109706      7805jm1 102791      23252t4 105312      6477lz6 

here code have now:

sub acct_search()      dim searchresult range     dim x integer      x = 3      ' search "activity" , store in range     set searchresult =        worksheets("data").range("a1:a3500").find(what:=worksheets("search").range("b3"), _                      lookin:=xlformulas, lookat:=xlpart, searchorder:=xlbyrows, _                      searchdirection:=xlnext, matchcase:=false, _                      searchformat:=false)      ' store address of first occurrence of word     firstaddress = searchresult.address              ' set value in o column, using row number , column number         worksheets("search").cells(x, 4) = searchresult.offset(0, 1).value          ' increase counter go next row         x = x + 1          ' find next occurrence of "activity"         set searchresult = cells.findnext(searchresult)          ' check if value found , not first value found     loop while not searchresult nothing , firstaddress <> searchresult.address  end sub 

when debug points x = x + 1 line. right able copy , paste first value without issue after point error comes play.

your problem changed because not resetting origin point of search after:=... parameter of range.findnext method. yes, passing in searchresult not accepting after:= parameter.

when ran code, thrown infinite loop due findnext finding same second instance. explains integer coughing @ being incremented above 2¹⁵. when changed long, gave else time choke.

after changed 1 line definitively include named parameter, cleared up.

  set searchresult = cells.findnext(after:=searchresult) 

this reproducible adding/removing parameter designation. seems cells.findnext(searchresult) finding search!b3 , since wasn't firstaddress, kept looping on same search!b3. wasn't until forced after:=searchresult .findnext adjusted itself. it's times these think fondly of c/c++ days without wallowing overhead.

i've gone through code , added with ... end with block should discourage questionable parentage.

sub acct_search()      dim searchresult range, firstaddress string     dim x long, ws worksheet      x = 3     set ws = worksheets("search")      ' search "activity" , store in range     worksheets("data").range("a1:a3500")         set searchresult = .find(what:=ws.range("b3"), lookin:=xlformulas, after:=.cells(.rows.count, .columns.count), _                                  lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _                                  matchcase:=false, searchformat:=false)          ' store address of first occurrence of word         firstaddress = searchresult.address                     ' set value in o column, using row number , column number             ws.cells(x, 4) = searchresult.offset(0, 1).value              ' increase counter go next row             x = x + 1              ' find next occurrence of "activity"             set searchresult = .findnext(after:=searchresult)             'debug.print searchresult.address(0, 0, external:=true)              ' check if value found , not first value found         loop while not searchresult nothing , firstaddress <> searchresult.address     end      set ws = nothing  end sub 

i've left after:= parameter designation in although no longer needed.