Find date problem---c is still nothing

[poorwallace]

I've got a small macro here which worked on my home computer but is not working here at work....I can't seem to figure out why (it looks good to me). I was hoping somebody could figure out where I'm mistepping. The problem, specifically, is that at the "set c" part of the code, c is not being found, when it clearly is in the range...I've gone to the immediate window and checked.....

Thanks

Sub test()

Dim todaydate
todaydate = Date

With Sheets("Supplement").Range("A1:A10000")
    Set c = .Find(todaydate, LookIn:=xlValues)
 
    If Not c Is Nothing Then
        c.Offset(0, 7).Copy
        c.Offset(0, 7).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    End If
 
End With

End Sub

 

[Andrew Poulsom]

Try:

Set c = .Find(todaydate, LookIn:=xlFormulas)

It worked for me.

 

[poorwallace]

Thanks! That worked! However, why is it that the xlformulas worked, while couldn't use xlValues? Clicking into the cell, it is not a formula...Am I not understanding something fairly simple here (it wouldn't be the first time )...If anyone could give a possible explanation to me why xlFormula worked when xlValues did not (though it did on my home computer), I would really appreciate it.

Anyways, thanks!

 

[Andrew Poulsom]

It's just a quirk:

http://www.cpearson.com/excel/DateTimeVBA.htm#Finding

 

[Gates Is Antichrist]

Nice work there, Andrew. That was a pretty hairy dilemma you squashed.