Cells.Find bug that's very strange

[Guest]

I have named a string variable strCurrentDate that concatenates data together
to form the following date format: dd/mm/yyyy. What it is then meant to do is
go to a workbook and find that date in that format. So for example
01/06/2007. The macro is getting as far as activating the workbook and
putting the date in the find function but is then coming back with:

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

What is happening here? Why can't it find the date that I'm looking for?

My line of code is:

Cells.Find(strCurrentDate).Activate

That is all. When the macro pauses due to the bug I can hover over the
strCurrentDate and it will show me the correct date (01/07/2007 for example)
so there's nothing wrong with my variable. That is the exact same date
format that I need to find in the workbook. When I stop the macro I can go
in to the workbook and hit Ctrl+F and 01/07/2007 will already be in the find
box. Hitting Find Next will find the correct cell. So why isn't the macro
doing it for me.

Tim Zych told me yesterday to try:

Cells.Find (CDate(strCurrentDate))

I tried that code but it seems to change the format of the date to the
American format and it doesn't find the cell. But it doesn't stop the macro
with a bug either so maybe you're on the right lines. But when I go to the
workbook and hit Ctrl+F the date in the find box is 7/1/2006 rather than the
way I wanted it as 01/07/2007. So it prevents the bug but doesn't find the
correct cell because the date format has been changed to the wrong format
from the correct format.

 

[Bob Phillips]

Try this sort of approach

stCurrentdate = DateSerial(2007, 10, 12)

If stCurrentdate = "False" Then Exit Sub

stCurrentdate = Format(stCurrentdate, "Short Date")

On Error Resume Next
Set cell = Cells.Find(What:=CDate(stCurrentdate),
After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)
On Error GoTo 0

If cell Is Nothing Then
MsgBox "Date cannot be found"
End If


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

Does that not just assume that you might not be able to find the data? I
may not have explained properly. Although the macro is not finding the date
(01/07/2007) it is definitely in the workbook. As I said, it goes as far as
populating the Find box with 01/07/2007 but then does not execute the search.
But when I go and do it manually it finds it.

 

[Bob Phillips]

Well it does, but it also help you to find it better.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)