Results 1 to 3 of 3

Thread: Find in VBA

  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    I recorded a macro which worked the first time but now it get an error - error 91 - 'object variable or with block variable not set'

    Cells.Find(What:="Sheet No :", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    I just can't figure out what is causing the error. I recorded a new macro which again runs the first time and then gets the error.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You get that error if the search does not find anything since it can not activate nothing

    You could try something like:

    Code:
    Dim rFind As Range
    Set rFind = Cells.Find(What:="Sheet No :", _
      After:=ActiveCell, LookIn:=xlValues, _
      LookAt:=xlPart, SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, MatchCase:=False, _
      SearchFormat:=False)
    
    If rFind Is Nothing Then
      MsgBox "not found"
    Else
      rFind.Activate
    End If
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Brilliant, thankyou very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •