Trouble with Find Method

[Alan M]

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

 

[K Dales]

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...

 

[Tom Ogilvy]

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

 

[Alan M]

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

 

[Tom Ogilvy]

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.