FIND (VBA) Time doesn't work for a specific value

[skodric]

Hi!
I have a LOG sheet with a B column which contains different timestamps in format (hh:mm:ss). I am trying to find and select the time that is entered in "General" sheet in the cell B2.
I accomplished this with following code, which works fine most of the time.
But when startTime is somewhere in-between 13:59:14 and 14:00:00, instead of selecting the cell with time 14:00:00 it selects the cell with time 12:00:00
I just can't figure it out why it works for most of the times and not for that range (13:59:14 - 14:00:00)
I would really appreciate help on the matter.

Sub TestFindFunction()

Dim startTime As Date
Dim B As Range

startTime = Sheets("General").Range("B2")
Set B = Range("B:B")
Set B = B.Find(what:=startTime)
B.Select

End Sub

 

[BSALV]

Are you sure the timestamps (search value and column B) are numbers, not strings for example.

But you say he finds the cell "12:00:00", bizare.

 

[skodric]

Are you sure the timestamps (search value and column B) are numbers, not strings for example.

But you say he finds the cell "12:00:00", bizare.

All the cells in column B are formatted as time.

 

[BSALV]

with date, sometimes you can have problems when the format is different.
Best is to change it to "general", do the search and then return to the previous format, but that's unlikely.
So this is in VBA and reads the dates, currency etc as doubles

Sub TestFindFunction()
     startTime = Range("A1").Value2                             'searchvalue (value2 !)
     Set B = Range("B:B")                                       'search range
     arr = B.Value2                                             'read to an array
     r = Application.Match(startTime, arr, 0)                   'find match
     If IsNumeric(r) Then B.Cells(r).Select                     'goto match
End Sub

 

[skodric]

with date, sometimes you can have problems when the format is different.
Best is to change it to "general", do the search and then return to the previous format, but that's unlikely.
So this is in VBA and reads the dates, currency etc as doubles

Sub TestFindFunction()
     startTime = Range("A1").Value2                             'searchvalue (value2 !)
     Set B = Range("B:B")                                       'search range
     arr = B.Value2                                             'read to an array
     r = Application.Match(startTime, arr, 0)                   'find match
     If IsNumeric(r) Then B.Cells(r).Select                     'goto match
End Sub

Thank you for this post.
I was trying to fiddle around with that, but still I couldn't achieve what I was aiming.
So right now, this for me stays mystery, why the FIND function actually finds very different time from the original (12:00:00 instead of the 14:00:00)