VBA Find method returns wrong time

[ratiogreg]

Hello guys. This is my first post although I have been visiting this site almost daily lately. So first of, thanks everyone for the great insight.
Here is my question. In an excel column B, i have dates ("Custom" as per Excel) presented as such:

time starting 9:15
5/6/2010 11:00:00 AM
5/6/2010 11:15:00 AM
5/6/2010 11:30:00 AM
5/6/2010 11:45:00 AM
5/6/2010 12:00:00 PM
5/6/2010 12:15:00 AM
5/6/2010 12:30:00 AM
5/6/2010 12:45:00 AM
5/6/2010 13:00:00 PM
5/6/2010 13:15:00 AM
5/6/2010 13:30:00 AM
5/6/2010 13:45:00 AM
5/6/2010 14:00:00 PM
time ending 16:15

Each time actually repeats itself many times before moving to the next 15min block.

I am using a the Find function to return the row of the first time a certain time is encountered in my column. For instance

Dim TimeSection As Long
Range("B1").Select
TimeSection = CalcSheet.Columns(2).Find(What:=Time, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

"Time" is a date type, and is expressed as a 12h format. It is obtained from using Timevalue() on another variable.

.Find returns the right result for most time, but not for certain. For example, if Time is 2:15pm, it will not return the row where 14:00 is. It will find 12:15 instead, as it doesnt care about the "1" in front of the "2:15". The same happens with 13:30 (1:30pm) for instance, which will be read as 11:30am. I undertand Find finds the first occurence of the searched item, which is great, but this seems weird since dates/times are serial numbers.

I have been trying to use Time as a string instead, using converting whatever time variable in need to find with FormatDateTime(___, vbShortTime), however the find function doesn't recognize the string as my cells are dates.

I have been using Find to gain speed as I have to perform the search many times on thousands of workbooks, until I realized it skipped all the 13:00 -14:00 time range.

Is the answer to use something else other than Find?

Any help appreciated!

Greg

 

[JoeMo]

Time is a VBA function that returns the current system time. You might want to choose another variable name for your code like myTime and see if that resolves the problem.

Also, keep in mind that a combination of date and time like 11/9/2011 11:15:00 PM is seen by excel as 40856.9687450232 which is the elapsed number of days since Jan 1, 1900 (for Windows-based system). The fraction is the time represented as a fraction of 1 day.

 

[GTO]

Hi there,

You already started this thread under http://www.mrexcel.com/forum/showthread.php?t=591218

 

[AlphaFrog]

Search for the time using the same time format as displayed in column B

TimeSection = CalcSheet.Columns(2).Find(What:=[COLOR="Red"]Format(MyTime, "h:mm")[/COLOR], After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

Note: Used MyTime as the time variable.

 

[AlphaFrog]

One other thing...
LookIn:=xlValues

TimeSection = CalcSheet.Columns(2).Find(What:=[COLOR="Red"]Format(MyTime, "h:mm")[/COLOR], After:=ActiveCell, [COLOR="Red"]LookIn:=xlValues[/COLOR], LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

 

[ratiogreg]

GTO, I just saw the duplicate. I didn't do this voluntarily. Sorry.

 

[ratiogreg]

AlphaFrog,
Thanks for the input. None of these seem to work tho. I got the lookin Xlformulas from another post where someone had trouble using find with dates and time. GTO solved my problem with using Date+Time as inputs. Thank you guys, i didnt expect an answer so fast.

 

[GTO]

Hey Greg,

If I sounded terse at all, it is I who apologize. I was just wanting to advise your of the 'lost' thread.

I re-read and spotted you want to find the first occurrance of a time. Is this solved or would you like a couple of suggestions?

Mark

 

[ratiogreg]

All good, it works.
I remembered i edited my post. the duplicate must have happened at that time.