Problem with using variable in Find statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
That did not work. I still receive the error "Object variable not set". Thank
you for the help, though.
 
That did the trick! Thank you for the help!

Tom Ogilvy said:
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
Click to expand...
 
Back
Top