Search Error -- Object Variable or with block variable not set



Hi,

I am searching for certain text in a given sheet.

 I am looking for cells that contain certain names e.g. Alfred, Ralph, Emily, Joe. I have these names in a separate worksheet and VB loops through and searches for these names.


However, it crashes anytime it cannot find any of these. Is there an easy way to get around this?


The ff will not work if "Alfred" is not in the worksheet that I am searching.



Cells.Select
    Selection.Find(What:="Alfred", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
       
       
       
    Selection.Find(What:="Ralph", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate


    Selection.Find(What:="Emily", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate



I am curious if there is some conditional code that I can write to get around this or should I change the syntax.


TIA





amargolisAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

dim f as Range
set f =    Selection.Find(What:="Emily", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
if not(f is nothing) then
  f.Activate
end if
0
 
amargolisAuthor Commented:
Thanks a lot. It works!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.