excel - What have I messed up in the VBA loop for each worksheet? -

i have send multiple letters out @ 1 time , replace 1 or 2 words within cell. problem need words bolded , tedious use macro individually on 150 worksheets. new coding , have tried search online edit code loop through of worksheets, try seems change current sheet on. below current code thought cause loop, instead of looping through worksheets seems loop through single worksheet on, asking if bold word on sheet.

origanal code:

sub findandbold() dim ws worksheet dim sfind string dim rcell range dim rng range dim lcount long dim ilen integer dim ifind integer dim istart integer  on error resume next set rng = activesheet.usedrange. _   specialcells(xlcelltypeconstants, xltextvalues) on error goto errhandler if rng nothing     msgbox "there no cells text"     goto exithandler end if  sfind = inputbox( _   prompt:="what want bold?", _   title:="text bold") if sfind = ""     msgbox "no text listed"     goto exithandler end if  ilen = len(sfind) lcount = 0  each rcell in rng     rcell         ifind = instr(.value, sfind)         while ifind > 0             .characters(ifind, ilen).font.bold = true             lcount = lcount + 1             istart = ifind + ilen             ifind = instr(istart, .value, sfind)         loop     end next  if lcount = 0     msgbox "there no occurrences of" & _       vbcrlf & "' " & sfind & " '" & _       vbcrlf & "to bold." elseif lcount = 1     msgbox "one occurrence of" & _       vbcrlf & "' " & sfind & " '" & _       vbcrlf & "was made bold." else     msgbox lcount & " occurrences of" & _       vbcrlf & "' " & sfind & " '" & _       vbcrlf & "were made bold." end if  exithandler:     set rcell = nothing     set rng = nothing     exit sub  errhandler:     msgbox err.description     resume exithandler end sub 

my recent attempt:

sub findandbold() dim ws worksheet dim sfind string dim rcell range dim rng range dim lcount long dim ilen integer dim ifind integer dim istart integer each ws in activeworkbook.worksheets     on error resume next     set rng = activesheet.usedrange. _       specialcells(xlcelltypeconstants, xltextvalues)     on error goto errhandler     if rng nothing         msgbox "there no cells text"         goto exithandler     end if      sfind = inputbox( _       prompt:="what want bold?", _       title:="text bold")     if sfind = ""         msgbox "no text listed"         goto exithandler     end if      ilen = len(sfind)     lcount = 0      each rcell in rng         rcell             ifind = instr(.value, sfind)             while ifind > 0                 .characters(ifind, ilen).font.bold = true                 lcount = lcount + 1                istart = ifind + ilen                ifind = instr(istart, .value, sfind)            loop        end    next      if lcount = 0         msgbox "there no occurrences of" & _           vbcrlf & "' " & sfind & " '" & _           vbcrlf & "to bold."     elseif lcount = 1         msgbox "one occurrence of" & _           vbcrlf & "' " & sfind & " '" & _           vbcrlf & "was made bold."     else         msgbox lcount & " occurrences of" & _           vbcrlf & "' " & sfind & " '" & _          vbcrlf & "were made bold."     end if next ws exithandler:     set rcell = nothing     set rng = nothing     exit sub  errhandler:     msgbox err.description     resume exithandler end sub 

corrected working code provided provided yowe3k:

sub findandbold() dim ws worksheet dim sfind string dim rcell range dim rng range dim lcount long dim ilen integer dim ifind integer dim istart integer     each ws in activeworkbook.worksheets     set rng = nothing     set rng = ws.usedrange.specialcells(xlcelltypeconstants, xltextvalues)     if rng nothing         msgbox "there no cells text"         goto exithandler     end if      sfind = inputbox( _       prompt:="what want bold?", _       title:="text bold")     if sfind = ""         msgbox "no text listed"         goto exithandler     end if      ilen = len(sfind)     lcount = 0      each rcell in rng         rcell             ifind = instr(.value, sfind)             while ifind > 0                 .characters(ifind, ilen).font.bold = true                 lcount = lcount + 1                istart = ifind + ilen                ifind = instr(istart, .value, sfind)            loop        end    next      if lcount = 0         msgbox "there no occurrences of" & _           vbcrlf & "' " & sfind & " '" & _           vbcrlf & "to bold on worksheet '" & ws.name & "'."     elseif lcount = 1         msgbox "one occurrence of" & _           vbcrlf & "' " & sfind & " '" & _           vbcrlf & "was made bold on worksheet '" & ws.name & "'."     else         msgbox lcount & " occurrences of" & _           vbcrlf & "' " & sfind & " '" & _          vbcrlf & "were made bold on worksheet '" & ws.name & "'."     end if next ws exithandler:     set rcell = nothing     set rng = nothing     exit sub  end sub 

you setting loop go through each worksheet (using ws reference sheet being processed), processing range on activesheet. use ws instead of activesheet.

you should set rng nothing before attempting set usedrange.specialcells or else, if crashes, if rng nothing then statement won't work (because rng still set whatever set on previous iteration through loop).

'... each ws in activeworkbook.worksheets     set rng = nothing     on error resume next     set rng = ws.usedrange.specialcells(xlcelltypeconstants, xltextvalues)     on error goto errhandler     if rng nothing '...