I've been trying to get a FIND working in Excel 2003 which I remember
working fine earlier, but for some reason this attempt has been an
effort in futility. Here's the code generated by recording a macro and
CTRL-F to find the text I'm looking for:

Cells.Find(What:="2005.12.07", After:=ActiveCell, LookIn:=xlValues,
_
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Running that code immediately after generating it returns an error! So
does every attempt at using it. I cannot believe that I've discovered
a bug. It works fine from the UI, but dies in VBA.

HELP!

Thanks;
Duncan

Give this a try...

dim rng as range

set rng = activesheet.Cells.Find(What:="2005.12.07", After:=ActiveCell,
LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

if rng is nothing then
msgbox "Sorry not found..."
else
rng.select
end if
--
HTH...

Jim Thomlinson


"Duncan A. McRae" wrote:

I've been trying to get a FIND working in Excel 2003 which I remember
working fine earlier, but for some reason this attempt has been an
effort in futility. Here's the code generated by recording a macro and
CTRL-F to find the text I'm looking for:

Cells.Find(What:="2005.12.07", After:=ActiveCell, LookIn:=xlValues,
_
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Running that code immediately after generating it returns an error! So
does every attempt at using it. I cannot believe that I've discovered
a bug. It works fine from the UI, but dies in VBA.

HELP!

Thanks;
Duncan

Dates and VBA are trouble.

I'd try this:

dim FoundCell as range
set foundcell = Cells.Find(What:=dateserial(2005,12,07), _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

if foundcell is nothing then
msgbox "failed"
else
foundcell.activate
end if



"Duncan A. McRae" wrote:

I've been trying to get a FIND working in Excel 2003 which I remember
working fine earlier, but for some reason this attempt has been an
effort in futility. Here's the code generated by recording a macro and
CTRL-F to find the text I'm looking for:

Cells.Find(What:="2005.12.07", After:=ActiveCell, LookIn:=xlValues,
_
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Running that code immediately after generating it returns an error! So
does every attempt at using it. I cannot believe that I've discovered
a bug. It works fine from the UI, but dies in VBA.

HELP!

Thanks;
Duncan

--

Dave Peterson

Try something like:

Dim dt as Date
Dim rng as Range

dt = DateValue("12/07/2005")
set rng = cells.Find(what:=clng(dt), After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If rng is nothing then
msgbox "Not found"
exit sub
End if

--
Regards,
Tom Ogilvy



"Duncan A. McRae" wrote in message
oups.com...
I've been trying to get a FIND working in Excel 2003 which I remember
working fine earlier, but for some reason this attempt has been an
effort in futility. Here's the code generated by recording a macro and
CTRL-F to find the text I'm looking for:

Cells.Find(What:="2005.12.07", After:=ActiveCell, LookIn:=xlValues,
_
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Running that code immediately after generating it returns an error! So
does every attempt at using it. I cannot believe that I've discovered
a bug. It works fine from the UI, but dies in VBA.

HELP!

Thanks;
Duncan

rng still comes back empty (= nothing).

Nicely done! This whole time I thought I was looking at a string, when
in fact it was a date. I thank you, sir!

Here's what I'm going with:

Dim FoundCell As Range
Set FoundCell = Cells.Find(Date)

If FoundCell Is Nothing Then
MsgBox "failed"
Else
FoundCell.Activate
End If

..Find remembers the last parms that were passed to it--either in code or by the
user. I'd explicitly specify each of those .find parms if I were you.

And if you ever have trouble...

Set FoundCell = Cells.Find(clng(date), ......

may help.


"Duncan A. McRae" wrote:

Here's what I'm going with:

Dim FoundCell As Range
Set FoundCell = Cells.Find(Date)

If FoundCell Is Nothing Then
MsgBox "failed"
Else
FoundCell.Activate
End If

--

Dave Peterson