Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cells.Find not working (Excel 2K)

    For some reason, all of my Cells.Find code stopped working. I get a Run-time error 91 message: Object variable or With block variable not set. The exact same line of code worked last week but not this week. I haven't had any upgrades or system or application changes in the interim.

    My best guess is I'm missing an Object Library, though VBA, MS Excel 9.0, OLE Automation, and MS Office 9.0 are loaded.

    Any idea on what I'm missing?

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells.Find not working (Excel 2K)

    Are you sure that you've got all the same code modules loaded - and that all of it is EXACTLY the same as it was the last time you ran the procedure? If you are, then you'll need to let us see your "Cells.Find" code - or give us a reference to a previous post in the Lounge. Otherwise, we'll be guessing in the dark. HTH
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells.Find not working (Excel 2K)

    Without seeing your code it is hard to troubleshoot...

    Note that the find method remembers the options set in your last search, whether it was done through VBA or through the UI doesn't matter. So if the last time you did a search you used the values option, your code will be looking in values too, unless you use the relevant arguments to the Find method.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells.Find not working (Excel 2K)

    Okay, good point: You need to see the code:

    Dim dtmPeriod As Date
    dtmPeriod = DateSerial(Year(Date), Month(Date) - 1, 1)
    Cells.Find(dtmPeriod, , , xlWhole, xlByColumns, xlNext).Activate

    In writing the rest of my explanation to the two of you, I realized that the error message: "Run-time error '91': Object variable or With block variable not set." is the one that pops when it can't find what it's looking for. I've used error-trapping code to handle this in the past and forgot that about error 91. I'm embarrassed. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I changed the critical lines to:

    dtmPeriod = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "m/d/yyyy")
    Cells.Find(dtmPeriod, , , xlWhole, xlByColumns, xlNext).Activate

    and it works just fine.

    Thanks for your trouble!

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells.Find not working (Excel 2K)

    I had the same problem several months ago. I posted it in the Excel group. What I found was that I had the column width set to narrow and was getting "####" in some cells. For Find to work it must be able to "see" the entire string. Try to adjust your column width. Another option would be to use the Match function.

    HTH
    Thom

    Oooops! Sorry didn't notice you found the problem.

Posting Permissions

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