Query on small piece of code
 

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

Query on small piece of code
 

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


"Mike" wrote in message
...
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

Query on small piece of code
 

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


"Mike" wrote in message
...
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


"Mike" wrote in message
...
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

Query on small piece of code
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

Mike wrote:
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

Query on small piece of code
 

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


"Chip Pearson" wrote in message
...
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


"Mike" wrote in message
...
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


"Mike" wrote in message
...
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

Query on small piece of code
 

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

--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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


"Mike" wrote in message
...
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

Query on small piece of code
 

cdate should respect your regional settings.

--
Regards,
Tom Ogilvy

"Mike" wrote in message
...
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


"Chip Pearson" wrote in message
...
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


"Mike" wrote in message
...
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


"Mike" wrote in message
...
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

Query on small piece of code
 

Cheers Tom and Chip - much appreciate the help

Mike


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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


"Mike" wrote in message
...
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