Cells.Find not working (Excel 2K)

[OfficeProWannabe]

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?

[unkamunka]

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

[pieterse]

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.

[OfficeProWannabe]

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!

[Thom Peterson]

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.