Finding Dates In VBA

[xlbo]

All - this is more to raise a bit of a debate 'cos I'm sure there's a better way of doing this.

The problem:
Using .Find syntax where dates are involved

Whatever I use (value/text/formula) and whatever I try and look in (xlvalues / xlformulas), dates entered into either cells or textboxes are impossible to .find

I have resorted to having a list of Date serial numbers next to my dates column and using that as the find column for date searches. Obviously, this not only means that there is an extra, unwanted column in my spreadsheet but also, I have to add extra lines to my code to convert a textually entered date to a value (to .find it) and back again (to do anything with it)

Any ideas on this - there must be a better way...something I've missed

TIA Rgds
~Geoff~

 

[BrainB]

I wonder if youare formating your date correctly. This works OK for me (XL97):=
'-------------------------------------------
Sub test()
Dim FoundCell
Dim MyDate As Date
MyDate = #1/1/01#
'-------------------
Set FoundCell = ActiveSheet.Cells.Find(what:=MyDate)
If FoundCell Is Nothing Then
MsgBox ("Not Found&quot
Else
MsgBox (FoundCell.Address)
End If
End Sub
'--------------------------------------------
Regards
BrianB
** Let us know if you get something that works !
================================

 

[xlbo]

rMikeSmith has sent me something off list which is good - picks up the format of the column before searching. I thought it was probably to do with formats but I know how the cells are formatted and still couldn't get it to work - got a nice little routine from Mike (Robert ?) tho - if you wanna post in this thread I'll give you a star ;-)
Cheers Rgds
~Geoff~

 

[Loomah]

xlbo
I'm guessing this is a result of my blunder yesterday?
Having changed it slightly this will find a date but the select fails, which is be(a)musing.

Sub lime()
Dim lRow_1 As Long
Dim lRow_2 As Long
Dim myDate As Date

myDate = CDate(InputBox("enter date", "date"))
lRow_1 = Sheets(2).Cells.Find(what:=myDate).Row
lRow_2 = lRow_1 + 10
Range(Cells(lRow_1, 1), Cells(lRow_2, 1)).Select
End Sub

If this can be easily adapted to fit the original funk boy's query would someone post it back for me.

muchas ta
Happy Friday
;-) If a man says something and there are no women there to hear him, is he still wrong?