Problem with using variable in Find statement
 

I am writing a macro to locate the current date (in form mm/dd/yy) from a
column of dates in column A1. I am using a variable that is set to the value
of cell G1 that uses the NOW function to return the current date. However,
the Find method does not seem to like a variable. Below is the code I am
currently using to locate the current date. When I run this I get an error,
please help!

Dim LookFor
LookFor = Range("G1").Select
Range("A1").Select
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=_
False).Activate

Problem with using variable in Find statement
 

try replacing

LookFor = Range("G1").Select

with

LookFor = Range("G1")


Lee-Z



"DOOGIE" wrote in message
...
I am writing a macro to locate the current date (in form mm/dd/yy) from a
column of dates in column A1. I am using a variable that is set to the
value
of cell G1 that uses the NOW function to return the current date. However,
the Find method does not seem to like a variable. Below is the code I am
currently using to locate the current date. When I run this I get an
error,
please help!

Dim LookFor
LookFor = Range("G1").Select
Range("A1").Select
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=_
False).Activate

Problem with using variable in Find statement
 

Now returns a date and time. If you values in column A are just Dates, your
value will not be found.

Dim LookFor as Long
Dim rng as Range
LookFor = clng(Range("G1"))
set rng = Columns(1).Find(What:=LookFor, After:=Range("A1"), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Select
else
msgbox format(Lookfor,"mm/dd/yy") & " was not found"
End if

--
Regards,
Tom Ogilvy


"DOOGIE" wrote in message
...
I am writing a macro to locate the current date (in form mm/dd/yy) from a
column of dates in column A1. I am using a variable that is set to the
value
of cell G1 that uses the NOW function to return the current date. However,
the Find method does not seem to like a variable. Below is the code I am
currently using to locate the current date. When I run this I get an
error,
please help!

Dim LookFor
LookFor = Range("G1").Select
Range("A1").Select
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=_
False).Activate

Problem with using variable in Find statement
 

That did not work. I still receive the error "Object variable not set". Thank
you for the help, though.

"Lee-Z" wrote:

try replacing

LookFor = Range("G1").Select

with

LookFor = Range("G1")


Lee-Z



"DOOGIE" wrote in message
...
I am writing a macro to locate the current date (in form mm/dd/yy) from a
column of dates in column A1. I am using a variable that is set to the
value
of cell G1 that uses the NOW function to return the current date. However,
the Find method does not seem to like a variable. Below is the code I am
currently using to locate the current date. When I run this I get an
error,
please help!

Dim LookFor
LookFor = Range("G1").Select
Range("A1").Select
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=_
False).Activate

Problem with using variable in Find statement
 

That did the trick! Thank you for the help!

"Tom Ogilvy" wrote:

Now returns a date and time. If you values in column A are just Dates, your
value will not be found.

Dim LookFor as Long
Dim rng as Range
LookFor = clng(Range("G1"))
set rng = Columns(1).Find(What:=LookFor, After:=Range("A1"), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Select
else
msgbox format(Lookfor,"mm/dd/yy") & " was not found"
End if

--
Regards,
Tom Ogilvy


"DOOGIE" wrote in message
...
I am writing a macro to locate the current date (in form mm/dd/yy) from a
column of dates in column A1. I am using a variable that is set to the
value
of cell G1 that uses the NOW function to return the current date. However,
the Find method does not seem to like a variable. Below is the code I am
currently using to locate the current date. When I run this I get an
error,
please help!

Dim LookFor
LookFor = Range("G1").Select
Range("A1").Select
Cells.Find(What:=LookFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=_
False).Activate

Problem with using variable in Find statement
 

read what Tom Ogilvy wrote that should fix your proble

--
reddog906
-----------------------------------------------------------------------
reddog9069's Profile: http://www.excelforum.com/member.php...fo&userid=2445
View this thread: http://www.excelforum.com/showthread.php?threadid=38989