Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Find Method
I am using the following to find today's date in a column containing
dates for every day of the year. I have defined the serxh target using the Dim ThisDate declaration but when I run the code , for some unexpklained reason , I receive an error message saying that the object is not defined. Can you shed any light on this simple problem please? Dim ThisDate As Range Dim I As Range Dim Current As Range Sheets(2).Unprotect Set ThisDate = Range("B3") 'Cell contains today's date With ActiveSheet.Range("A:A") ' Searches for todays date in Column A Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select Set Current = Selection |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Find Method
Which line throws the error? If it is on the .Find line, it could be because
it is not finding the date. If so, the result is "Nothing" and when you try to .Select a "Nothing" you will have a problem because there is no object to select. It is better to set the .Find result to a variable first, test it for Nothing, and then .Select (or whatever): Dim FoundIt as Range Set FoundIt = Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4) If Not (FoundIt Is Nothing) Then FoundIt.Select Else... -- - K Dales "Alan M" wrote: I am using the following to find today's date in a column containing dates for every day of the year. I have defined the serxh target using the Dim ThisDate declaration but when I run the code , for some unexpklained reason , I receive an error message saying that the object is not defined. Can you shed any light on this simple problem please? Dim ThisDate As Range Dim I As Range Dim Current As Range Sheets(2).Unprotect Set ThisDate = Range("B3") 'Cell contains today's date With ActiveSheet.Range("A:A") ' Searches for todays date in Column A Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select Set Current = Selection |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Find Method
Also, you have a with statement but don't use it - instead, the unqualifed
Cells searches the entire sheet. And the unqualified Cells(796) is probably not what you think. From the immediate window: ? cells(796).Address $AB$4 With Activesheet.Columns(1) set Current = .Cells.Find(What:=ThisDate, _ After:=.Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) End With if not current is nothing then set current = current.Offset(0, 4) else msgbox "Not found" end if -- Regards, Tom Ogilvy "Alan M" wrote in message ... I am using the following to find today's date in a column containing dates for every day of the year. I have defined the serxh target using the Dim ThisDate declaration but when I run the code , for some unexpklained reason , I receive an error message saying that the object is not defined. Can you shed any light on this simple problem please? Dim ThisDate As Range Dim I As Range Dim Current As Range Sheets(2).Unprotect Set ThisDate = Range("B3") 'Cell contains today's date With ActiveSheet.Range("A:A") ' Searches for todays date in Column A Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select Set Current = Selection |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Find Method
When the error is displayed the code screen show a highlight over the
'SearchFormay:= False' part of the code. I hope this helps. I do not know what else to suggest as the code should work but does not "K Dales" wrote: Which line throws the error? If it is on the .Find line, it could be because it is not finding the date. If so, the result is "Nothing" and when you try to .Select a "Nothing" you will have a problem because there is no object to select. It is better to set the .Find result to a variable first, test it for Nothing, and then .Select (or whatever): Dim FoundIt as Range Set FoundIt = Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4) If Not (FoundIt Is Nothing) Then FoundIt.Select Else... -- - K Dales "Alan M" wrote: I am using the following to find today's date in a column containing dates for every day of the year. I have defined the serxh target using the Dim ThisDate declaration but when I run the code , for some unexpklained reason , I receive an error message saying that the object is not defined. Can you shed any light on this simple problem please? Dim ThisDate As Range Dim I As Range Dim Current As Range Sheets(2).Unprotect Set ThisDate = Range("B3") 'Cell contains today's date With ActiveSheet.Range("A:A") ' Searches for todays date in Column A Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select Set Current = Selection |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Find Method
Did you record the code in xl2003 and are trying to use it in an earlier
version. SearchFormat was introduced in xl2003 and is not recognized in earlier versions. Since it is set to false, just remove that part of the code. -- Regards, Tom Ogilvy "Alan M" wrote in message ... When the error is displayed the code screen show a highlight over the 'SearchFormay:= False' part of the code. I hope this helps. I do not know what else to suggest as the code should work but does not "K Dales" wrote: Which line throws the error? If it is on the .Find line, it could be because it is not finding the date. If so, the result is "Nothing" and when you try to .Select a "Nothing" you will have a problem because there is no object to select. It is better to set the .Find result to a variable first, test it for Nothing, and then .Select (or whatever): Dim FoundIt as Range Set FoundIt = Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4) If Not (FoundIt Is Nothing) Then FoundIt.Select Else... -- - K Dales "Alan M" wrote: I am using the following to find today's date in a column containing dates for every day of the year. I have defined the serxh target using the Dim ThisDate declaration but when I run the code , for some unexpklained reason , I receive an error message saying that the object is not defined. Can you shed any light on this simple problem please? Dim ThisDate As Range Dim I As Range Dim Current As Range Sheets(2).Unprotect Set ThisDate = Range("B3") 'Cell contains today's date With ActiveSheet.Range("A:A") ' Searches for todays date in Column A Cells.Find(What:=ThisDate, _ After:=Cells(796), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 4).Select Set Current = Selection |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using variables to make a date and using find method to find that. | Excel Programming | |||
Find method | Excel Programming | |||
Find Method | Excel Programming | |||
with -.find end with method | Excel Programming | |||
Find method | Excel Programming |