Query on small piece of code

M

Mike

I have the following very simple piece of code as part of a macro:


Selection.Find(What:="01/08/2002", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate


Quite simply, in a row that is selected, the instruction is to activate the
cell where it finds the date shown.

I have recorded this through simply "doing a Ctrl+F" with the recorder
running, but when I run this macro to check it keeps failing.

Any ideas on what the problem is as it's driving me mad, I can't see what's
wrong.

Thanks in advance

Mike
 
M

Mike

I should say that the error message was as follows:

Run-time error '91':
Object variable or With block variable not set

Thanks again for any help

Mike
 
C

Chip Pearson

Mike,

You'll get this error if the value is not found. Are you sure the
value is in the range?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
F

Frank Kabel

Hi Mike
you probably have no cells selected when you invoke this macro which
contains your search string.
 
M

Mike

Chip

Thanks. Having thought about the value itself, I have just run it again
through the immediate window putting in the value "8/1/2002" and it now
works fine. The problem is that the date is actually formatted as a UK and
not American date. This seems to be the problem. This looks like it could
cause problems for me, is there any way of reverting all settings to a UK
setting, as it seems perhaps that with the visual basic it's running on a US
format, even if Excel itself isn't?

Regards

Mike
 
T

Tom Ogilvy

set rng = Selection.Find(What:=clng(cDate("01/08/2002")), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

if not rng is nothing then
rng.Select
Else
msgbox "Not found"
End if
 
Top