FindMethod-DateValues
 

Good Day Group,

Have a database looking as below. The cells of the dates are formated as
"Date".
The code below works fine except in the case that Textbox1 = 2004-01-08. The
FindMethod finds the date 2004-11-08 but ignores 2004-01-08. LenFunction
shows = 5 ie the DateValue figure. Any hints why this happens?

Brgds

CG Rosén
-------------------------------------------------------
1 2
2004-01-05 2004-11-08
2004-01-06 2004-11-09
2004-01-07 2004-11-10
2004-01-08 2004-11-11
2004-01-09 2004-11-12
2004-01-10 2004-11-13
2004-01-11 2004-11-14

---------------------------------------------------
Dim dton As String

dton = TextBox1.Text

With ActiveSheet.Range("A1:B8")

Set k = .Find(DateValue(dton), LookIn:=xlValues)
If Not k Is Nothing Then

MsgBox k.Column

End If
End With

FindMethod-DateValues
 

CG

It may be that Find is searching by row instead of by column. For any
arguments that you don't supply to Find, it will use the default. Except
that the default is not static, that is, it changes to whatever the last
Find you did either by UI or VBA. It's best when using the Find method to
supply all the arguments. The fifth argument is SearchOrder which you want
to be xlByColumns.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"CG Rosén" wrote in message
...
Good Day Group,

Have a database looking as below. The cells of the dates are formated as
"Date".
The code below works fine except in the case that Textbox1 = 2004-01-08.
The
FindMethod finds the date 2004-11-08 but ignores 2004-01-08. LenFunction
shows = 5 ie the DateValue figure. Any hints why this happens?

Brgds

CG Rosén
-------------------------------------------------------
1 2
2004-01-05 2004-11-08
2004-01-06 2004-11-09
2004-01-07 2004-11-10
2004-01-08 2004-11-11
2004-01-09 2004-11-12
2004-01-10 2004-11-13
2004-01-11 2004-11-14

---------------------------------------------------
Dim dton As String

dton = TextBox1.Text

With ActiveSheet.Range("A1:B8")

Set k = .Find(DateValue(dton), LookIn:=xlValues)
If Not k Is Nothing Then

MsgBox k.Column

End If
End With