Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Object Variable Not Set (Excel 2000)

    Hi All

    I'm trying to customise the "Find" feature in Excel 2000 to search for a name in the first column only of a spreadsheet but whenever the search finds nothing, I get the following Error Message 91 - "Object Variable or With Block Variable Not Set".

    I've tried running the following code by itself and/or as part of another macro and still get the same error message.

    Columns(1).Find(What:=(InputBox("Enter name for search.")), After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate

    When the search finds nothing, I want the macro to revert back to the above InputBox so I can try another variation of the name to search, or another name altogether, without having to click on the "End" or "Debug" button and run the macro again which is what happens at the moment. I've tried adding the line "On error GoTo 0" after the above code but it doesn't work.

    Incidentally, if the search does find the text I'm looking for, it's fine and completes the remainder of the macro without problem.

    I'm sure it's something very simple. Any help would be greatly appreciated!

    Best Christmas regards,

    Sue

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object Variable Not Set (Excel 2000)

    Hello Sue,

    Try something like this:

    Dim rng As Range
    Do While rng Is Nothing
    Set rng = ActiveSheet.Columns(1).Find(What:=(InputBox("Enter name for search.")), After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False)
    Loop
    rng.Activate

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object Variable Not Set (Excel 2000)

    YIPPEE! Many thanks, Hans. It works a treat. This has saved me about 8,000 clicks/keystrokes when I get back to work after Christmas!

    Very best regards,

    Sue

Posting Permissions

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