A problem with Range().Find(timeValue),it's so weird.

[Jinspet]

I have a column of data,they r time value. The range format is "hh:mm:ss".
Here is my column A:
11:59:13
12:35:00
12:59:23
13:00:00
13:34:20
16:24:09
My VBA code is:
'-------------------------------------
sub Test()

Dim x As Range
Set x = Range("a1").EntireColumn.Find(What:="12:59", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
MsgBox x.Address​

end sub
'-------------------------------------
when the argument "What" is bigger than "13:00" then the Find() will not working ,runtime error. Any suggestions ?

 

[Andrew Poulsom]

Maybe you can use:

Sub Test()
    Dim i As Long
    i = Application.Match(CDbl(TimeValue("13:00")), Range("A:A"))
    MsgBox Cells(i, 1).Address
End Sub

 

[Jinspet]

thanks a lot
your way is very good.
and i just solved my problem :
Range("a1").EntireColum.Find("13:00",Lookin:=xlValue) ,it works now,but thank you any way for replying my first post here.