Using Find with dates
 

I'm using the following, which is essentially a cut and paste for Excels VB
help, to search for a date held in a variable, CellDt.
I create CellDt with DateValue, so I know it's valid. Plus the dates in
column A are also valid (I've formatted the column to with Numbers |
Date...etc., and I've set a variable to the contents of a few random cells
and used TypeName to confirms it's a date.
But C is always set to nothing; it never changes.
If I change the range to the next column and search for a string, it does
find it (C is set to the value found), so it seems it has something to do
with the date(s) themselves...but I can't see what it might be.

With Worksheets(1).Range("A1:A119")
Set C = .Find(what:=CellDt, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
' copy found item to variable, etc.
' . . .
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < firstAddress

Any thoughts? And thanks.

Bert

Using Find with dates
 

Sub ZZZZ()
Dim celldt As Date
celldt = DateValue("1/1/2000")
With Worksheets(1).Range("A1:A119")
Set c = .Find(what:=celldt, _
After:=Worksheets(1).Range("A119"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With
End Sub


worked for me.

--
Regards,
Tom Ogilvy

"Bert" wrote in message
...
I'm using the following, which is essentially a cut and paste for Excels
VB
help, to search for a date held in a variable, CellDt.
I create CellDt with DateValue, so I know it's valid. Plus the dates in
column A are also valid (I've formatted the column to with Numbers |
Date...etc., and I've set a variable to the contents of a few random cells
and used TypeName to confirms it's a date.
But C is always set to nothing; it never changes.
If I change the range to the next column and search for a string, it does
find it (C is set to the value found), so it seems it has something to do
with the date(s) themselves...but I can't see what it might be.

With Worksheets(1).Range("A1:A119")
Set C = .Find(what:=CellDt, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
' copy found item to variable, etc.
' . . .
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < firstAddress

Any thoughts? And thanks.

Bert