Can't find today's date

O

Otto Moehrbach

Excel 2002, WinXP
I'm down to the last hairs on this one.
An OP has a list of March dates in Column C. He wants to find today's date
and place the contents of cell A1 into the cell to the right of today's
date. Simple enough. I write out a quick macro (below) and send it to him
via the newsgroup. It doesn't work for him. The error is that the Find
cannot find today's date.
I tell him to send me his file. Sure enough, the macro does not work on his
file.
The only difference between his and my Column Cs is this:
He entered 3/1/06 into C1. Then in C2 he put "=C1+1" (without the quotes)
and dragged that formula down 30 some cells.
I, on the other hand, entered 3/1/06 into C1 and dragged that cell down some
30 cells.
The macro works on my file but not on his. I entered the dates my way on
his file and all is well.
Why did the formula setup result in the error? Thanks for your help. Otto
Sub FindDate()
Dim TheRng As Range
Set TheRng = Range("C1", Range("C" & Rows.Count).End(xlUp))
TheRng.Find(What:=Date, LookAt:=xlWhole).Offset(, 1).Value =
Range("A1").Value
End Sub
 
D

Dave Peterson

Sometimes converting the date to a long will work better:

TheRng.find(what:=clng(date), ...

Sometimes using application.match (with clng()) works better than .find, too.
 
O

Otto Moehrbach

Thanks Dave. There is no end to learning. The CLng(Date) didn't work. The
Match with Date didn't work. The Match with CLng(Date) worked. Is there
some rationale to this? My engineering brain needs a reason when something
happens. Otto
 
D

Dave Peterson

Not that I know.

One more option is to pick up the numberformat from one of the cells to be
searched (if they have the same format) and use that.
 
O

Otto Moehrbach

Dave
You lost me there. What do you mean by "pick up the numberformat from
one of the cells to be searched"? Thanks for taking the time to help me.
Otto
 
D

Dave Peterson

Dim myNumberFormat as string
dim FoundCell as range
with .range("C:C")
mynumberformat = .cells(1).numberformat
set foundcell = .cells.find(what:=application.text(date,mynumberformat), ....

.....




Otto said:
Dave
You lost me there. What do you mean by "pick up the numberformat from
one of the cells to be searched"? Thanks for taking the time to help me.
Otto
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find the correct sheet then find a value on that sheet 8
Today's Date 1
Today's date 1
VBA for Dependents shortcut menu 0
IF AND Function Date Range Issues 6
Look up 6
Finding a date 15
Date find Macro 3

Top