Searching a date

[Neil Meehan]

When I try and search for a date on a calendar I have created, the FIND
cannot see the date when it is formatted to just show the day. ( ie
custom format is d for each cell so it appears just to show the
day/date of the month.) When I created the calender I started with a
serial number abd then created the next days by inputting "A!+1" to
increment the date. Is there any way to search for the underlying
serial number using the find. I have tried to use "look in values" but
it does not see it. Does formatting fundamentally change the underlying
value of the cell. When I drag a date into another cell and "paste
special" value, the cell seems to have the correct serial number. So my
question is- . Is there a way to search for the undelrying serial
number, even when the cell is formatted?

 

[Dave Peterson]

I put 04/30/2006 through 12/31/2006 in column A.

I formatted that column as: dddd
(to just show the day)

I did an edit|find (manually) and tried to find 5/6/2006. It failed.

But I could do edit|find and search for 05/06/2006 and it worked.

Are you specifying the date in the same way you see the value in the formula
bar?

But I had trouble in code.

This didn't return the found cell:

Option Explicit
Sub testme1()

Dim FoundCell As Range
Dim myDate As Date

myDate = DateSerial(2006, 5, 6)

With ActiveSheet
With .Range("a:a")
Set FoundCell = .Cells.Find(what:=CLng(myDate), _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox myDate & " wasn't found"
Else
MsgBox FoundCell.Address
End If
End Sub

But I could cheat--I turned the numberformat to general, searched, and the
changed the format back:

Option Explicit
Sub testme2()

Dim FoundCell As Range
Dim myDate As Date
Dim SavedFormat As String

myDate = DateSerial(2006, 5, 6)

With ActiveSheet
With .Range("a:a")
SavedFormat = .Cells(1).NumberFormat
.NumberFormat = "General"
Set FoundCell = .Cells.Find(what:=CLng(myDate), _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
.NumberFormat = SavedFormat
End With
End With

If FoundCell Is Nothing Then
MsgBox myDate & " wasn't found"
Else
MsgBox FoundCell.Address
End If
End Sub

=======

But searching for dates in code can be frustrating.

This worked fine (and I didn't expect it to):

Sub testme3()

Dim FoundCell As Range
Dim myDate As Date

myDate = DateSerial(2006, 5, 6)

With ActiveSheet
With .Range("a:a")
Set FoundCell = .Cells.Find(what:=myDate, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox myDate & " wasn't found"
Else
MsgBox FoundCell.Address
End If
End Sub