Error looking time "14:00" in VBA with . FIND function

Copper Contributor

Good morning
I have a strange problem. I found this problem in Excel 365 Pro e Excel 2016 Pro.

I have a column that contains hour value from 8:00 to 18:00 step by half hour. The cell is formatted with "*hh:mm" (time)

 

For Example 

 

8:00

8:30

.....

12:00

...

14:00

14:30

....

17:30

18:00

 

I tried to look for the cell that contains a time, for example "11:30" with this code:

TimeToFind= "08:00"
Set Position=worksheets("Foglio1").Range("E5:E21").Find(TimeValue(TimeToFind), LookIn:=xlFormulas)

 

All gone since I change then string TimeToFind with the "14:00" or "14:30" value.

 

TimeToFind= "14:00"
Set Position=worksheets("Foglio1").Range("E5:E21").Find(TimeValue(TimeToFind), LookIn:=xlFormulas)

 

When I change the string with one of this two values Position return the cell that contains "12:00" (instead "14:00") and a"12:30" (instead "14:30").

The problem happened with just this two values. Every other value gives the correct cell.

Someone find same problem or can replicate it?

 

Thanks

1 Reply
The problem was I didn't find the entire string with LookAt:=xlWhole. The system changed the "14:00" in "2.00 pm" and the first string it found was a partial string "2:00" in "12:00".