Results 1 to 8 of 8

Run-time error '91' when 'On error goto' and cells.find

Threaded View

  1. #1
    Registered User
    Join Date
    10-04-2008
    Location
    West Palm Beach
    Posts
    11

    Run-time error '91' when 'On error goto' and cells.find

    Hi all.

    I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:

    'Run-time error '91':
    Object variable or With block variable not set'

    Do I have to clear a buffer after each cells.find search?

    My code:

    Sub GetMobile()
    
    ' GetMobile Macro
    ' Macro recorded 9/30/2008 by ****
    '
    
    ' Fetch boat
        Sheets("Data").Select
      On Error GoTo JumpToCar
        Cells.Find(What:="Boat", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Select
      On Error GoTo 0
         Selection.Offset(0, 1).Range("a1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Main").Select
        Cells.Find(What:="Boat", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        Sheets("Data").Select
    
    ' Fetch car
    JumpToCar:
      On Error GoTo JumpToTrain
        Cells.Find(What:="car", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Select
      On Error GoTo 0
         Selection.Offset(0, 1).Range("a1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Main").Select
        Cells.Find(What:="car", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        Sheets("Data").Select
    
    ' Fetch train
    JumpToTrain:
      On Error GoTo JumpToPlane
        Cells.Find(What:="train", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Select
      On Error GoTo 0
         Selection.Offset(0, 1).Range("a1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Main").Select
        Cells.Find(What:="train", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        Sheets("Data").Select
    
    ' Fetch plane
    JumpToPlane:
      On Error GoTo JumpToBike
        Cells.Find(What:="plane", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Select
      On Error GoTo 0
         Selection.Offset(0, 1).Range("a1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Main").Select
        Cells.Find(What:="plane", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        Sheets("Data").Select
    
    ' Fetch bike
    JumpToBike:
      On Error GoTo JumpToExit
        Cells.Find(What:="bike", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Select
      On Error GoTo 0
         Selection.Offset(0, 1).Range("a1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Main").Select
        Cells.Find(What:="bike", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
    
    JumpToExit:
    
    End Sub

    Thanks in advance,


    Bjoern
    Last edited by bjoern; 10-09-2008 at 04:20 PM. Reason: Change status to solved.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. vba cells.find problem
    By jeternyn1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2007, 02:39 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1