Find date in a cell that uses a formula

[tbro]

I've tried googling and searching this forum for this problem. I bet I'm not the first to ask, but I'm having a hard time finding the answer.

Here's the issue:

Cell A1: 2012-10-07
Cell A2: =A1+1

Cell A2 will return 2012-10-08, which is today's date.
I want to find the cell containing today's date, in this example it is cell A2.

If A2 was, like A1, written: 2012-10-08, I could find it using:
Cells.Find(date).Activate
However, now that A2 has a formula, I somehow need to search for the returned value in the cell..?

The same thing applies for locating year+week, which in my workbook are written YYWW, ie 1240 for today's year and week.
The first cell contains 1201 and the following ones point to one cell back and adds 1. Just like the A2 example.

Please enlighten me. Almost all workbooks contain this type of "formula-date" and I need to be able to find them.

Thanks in advance.

Regards,
Tbro

 

[pooja_deshpande]

Cells.Find(date, , xlValues).Activate

 

[tbro]

Cells.Find(date, , xlValues).Activate

It works fine if I create a new excel sheet. Somehow, though, I get an error in my big workbook. I thought "make a button" and went:
Sub finddate()
Cells.Find(Date, , xlValues).Activate
End Sub
Why do I get an error 91 when trying to run the sub?
I even tried naming a cell by today's date, but I still get that error.

 

[tbro]

My bad.

The cells are formatted as DD-MMM, ie 08-oct
How will I find this value in an easy way?

 

[pooja_deshpande]

try Cells.Find(Text(date,"dd-mmm"), , xlValues).Activate

 

[tbro]

try Cells.Find(Text(date,"dd-mmm"), , xlValues).Activate

If put in an emtpy sub, 'Text' is marked saying "Compilation error: Sub or Function has not been defined".

 

[pooja_deshpande]

error error error