Solved: Locate Date in cell formatted as DD-MMM

[amw]

I'm looking for a way locate a cell containing today's date.

The cells are formatted as DD-MMM, ie 09-oct

Any ideas on how to get this to work?

[amw]

I solved the equation myself. Didn't know I could pick a format to search for.
Recorded a macro while I made a search for today's date. :doh:

Application.FindFormat.NumberFormat = "d-mmm"
Cells.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart _
, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=True).Activate

I'll bug this forum with other questions instead.. :yes

[Bob Phillips]

Maybe best to set FindFormat back to General after you are done.

[amw]

Good advice, xld.

Although I ran into another problem:

Cell A1 contains 2012-10-08
Cell A2 contains =A1+1
All these type of date cells are formatted as d-mmm as mentioned above.

I can't find 2012-10-09 in cell A2 if it contains that formula. At least not while searching as mentioned above. I doesn't work that way, I suppose.

So how does it work? :think:

[Bob Phillips]

Why is the format important if you just want to find today's date?

[amw]

Why is the format important if you just want to find today's date?
I thought the specific format was necessary in order to find the date.

If cell A1 contains 2012-10-08
and cell A2 contains A1+1
Then cell A2 contains today's date
But I can't find Date() if I do a regular search à la:
Cells.Find(Date).Activate

Do you follow? : pray2:

In my workbook I've set all cells to exact dates (as A1 above) instead of using the "previouscell"+1 attempt (as A2 above).
So I have a solution, but I'm still very curious how one would solve that issue.

[Bob Phillips]

If you ignore the format, this seems to work fine

Dim cell As Range

With ActiveSheet.UsedRange

Set cell = Nothing
On Error Resume Next
Set cell = .Cells.Find(What:=Date, _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
On Error GoTo 0

If Not cell Is Nothing Then

cell.Activate
End If
End With