Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable in if statement | Excel Worksheet Functions | |||
if than statement has too many variable | New Users to Excel | |||
Macro Creating Variable and using variable in a SQL statement | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |