excel - How to avoid need to activate worksheet every loop -

i've set vba code in excel asks user select second worksheet, searches value (a shared key linking 2 sets of data, found 6 columns after rng, want add retrieved value) in second table , adds value row column in original table. part of program adjust loop below.

it works fine if when leave in line activate curfile workbook. means screen flashing lot , forth between 2 workbooks. , once start getting hundreds or thousands of lines of data ridiculously slow.

when comment out line, value findcid doesn't change , seems keep on refilling same line, though value r updating. if after few loops add activate line in, resumes filling in results several lines down.

how can streamline this? using thisworkbook references explicitly defining curfile (curfile = activeworkbook.name) earlier doesn't seem go workbook next value search for, unless reactivate sheet.

do while r <= maxrows  workbooks(curfile).worksheets("sheet1")     set rng = .range(cells(r, c), cells(r, c)) end  findcid = rng.offset(0, 6).value  if trim(findcid) <> ""     workbooks(fn)   ' found earlier function        .activate     end      sheets("sheet1").range("d:d")         set foundcell = .find(what:=findcid)             if not foundcell nothing                 pathlen = foundcell.offset(0, 2).value   workbooks(curfile).sheets("sheet1").activate 'if comment out line doesn't work                 rng.value = pathlen                 msgbox "cid found in " & foundcell.address & " value " & pathlen             else                 msgbox "nothing found"             end if     end end if  on error resume next  r = r + 1 loop 

actually when working objects, in of cases, there no need activate workbooks\worksheets. code modifications in regard:

application.screenupdating = false '(as suggested cbrf23) '...... 'begining of code '......  while r <= maxrows      workbooks(curfile).worksheets("sheet1")         set rng = .cells(r, c) '(1)     end      findcid = rng.offset(0, 6).value2             if trim(findcid) <> ""         set foundcell = workbooks(fn).sheets("sheet1").range("d:d").find(what:=findcid)         if not foundcell nothing rng.value = foundcell.offset(0, 2).value2     end if      r = r + 1 loop '...... 'rest of code '...... application.screenupdating = true 

(1) notice way range defined it’s made of once cell; if range has more 1 cell i.e. cell(r,c) cell(r,c+5) need use form:

set rng = range(.cells(r, c), .cells(r, c+5))

there no need add period . before range range defined cells within range command. using period . before cell command referred part of

with workbooks(curfile).worksheets("sheet1")

however if range defined a1:f1 period . has added before range in:

set rng = .range(“a1:f1”)

i removed msgbox commands believe testing purposes. not showing these messages hundreds or thousands lines of data. isn’t it?