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

%3CLINGO-SUB%20id%3D%22lingo-sub-1363602%22%20slang%3D%22en-US%22%3EError%20looking%20time%20%2214%3A00%22%20in%20VBA%20with%20.%20FIND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363602%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%3CBR%20%2F%3EI%20have%20a%20strange%20problem.%20I%20found%20this%20problem%20in%20Excel%20365%20Pro%20e%20Excel%202016%20Pro.%3C%2FP%3E%3CP%3EI%20have%20a%20column%20that%20contains%20hour%20value%20from%208%3A00%20to%2018%3A00%20step%20by%20half%20hour.%20The%20cell%20is%20formatted%20with%20%22*hh%3Amm%22%20(time)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Example%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E8%3A00%3C%2FP%3E%3CP%3E8%3A30%3C%2FP%3E%3CP%3E.....%3C%2FP%3E%3CP%3E12%3A00%3C%2FP%3E%3CP%3E...%3C%2FP%3E%3CP%3E14%3A00%3C%2FP%3E%3CP%3E14%3A30%3C%2FP%3E%3CP%3E....%3C%2FP%3E%3CP%3E17%3A30%3C%2FP%3E%3CP%3E18%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20look%20for%20the%20cell%20that%20contains%20a%20time%2C%20for%20example%20%2211%3A30%22%20with%20this%20code%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22pln%22%3ETimeToFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%2208%3A00%22%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Position%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3Eworksheets%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22Foglio1%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERange%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22E5%3AE21%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ETimeValue%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ETimeToFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20LookIn%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ExlFormulas%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20gone%20since%20I%20change%20then%20string%20TimeToFind%20with%20the%20%2214%3A00%22%20or%20%2214%3A30%22%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22pln%22%3ETimeToFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%2214%3A00%22%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Position%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3Eworksheets%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22Foglio1%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERange%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22E5%3AE21%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ETimeValue%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ETimeToFind%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20LookIn%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ExlFormulas%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20change%20the%20string%20with%20one%20of%20this%20two%20values%20Position%20return%20the%20cell%20that%20contains%20%2212%3A00%22%20(instead%20%2214%3A00%22)%20and%20a%2212%3A30%22%20(instead%20%2214%3A30%22).%3C%2FP%3E%3CP%3E%3CSTRONG%3EThe%20problem%20happened%20with%20just%20this%20two%20values.%3C%2FSTRONG%3E%20Every%20other%20value%20gives%20the%20correct%20cell.%3C%2FP%3E%3CP%3ESomeone%20find%20same%20problem%20or%20can%20replicate%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1363602%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1363682%22%20slang%3D%22en-US%22%3ERE%3A%20Error%20looking%20time%20%2214%3A00%22%20in%20VBA%20with%20.%20FIND%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363682%22%20slang%3D%22en-US%22%3EThe%20problem%20was%20I%20didn't%20find%20the%20entire%20string%20with%20LookAt%3A%3DxlWhole.%20The%20system%20changed%20the%20%2214%3A00%22%20in%20%222.00%20pm%22%20and%20the%20first%20string%20it%20found%20was%20a%20partial%20string%20%222%3A00%22%20in%20%2212%3A00%22.%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
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".