Find time value in VBA

[jeffreybrown]

For the life of me can't figure this one out.

In Column A one of the values is 2:33:30 PM

In Range("D1") 2:33:30 PM

How come the code below will not find the value in column A?

I changed the value in D1 to the number 2 and placed a 2 in Column A and it finds the 2, just having no luck with a time.

Please Login or Register  to view this content.

Any thoughts?

[Leith Ross]

Hello Jeff,

Time values are the fractional portion of the Date/Time value. The integer portion is the date. The date portion must also be the same before the match is true. Have you looked at the date portion of each time value?

[keyston]

I can get your code to work if D1 has a Date format, and the cell you are searching for in column A has a Time format. I found no other combination of formats that gave positive and consistent results. And I cannot be sure that this always works. I have absolutely no idea why this combination appears to work when more logical ones fail.

[jeffreybrown]

Hi Leith/Keyston,

Thanks for the feeback.

@Leith...

Both time values are set to 0.606597222222222 and 0.606597222222222 and there is no date. I've tried many different combinations trying to incoroporate the MOD function, but no luck. I can put a whole number in column A and a whole number in D1 and it works. Must have to due with the matching of the formats much like chip pearson explains on his site about dates.

@Keyston...

I tried your method but no luck there either, but like you said, doesn't make much sense.

[Leith Ross]

Hello Jeff,

I got the Find method to work by changing the xlLookIn constant to xlFormulas. Why this constant works and xlValues doesn't will require a little more digging.

Please Login or Register  to view this content.

[jeffreybrown]

Hi Leith,

Thank you very much. Just added some format and it works great.

[keyston]

Leith, Jeff,

I had noted elsewhere on the Web the use of xlFormulas in finding dates, but it did - and still does - nothing in my case. Whether I use xlValues or xlFormulas in either of your codes, the only way I have so far been able to get a match to a formatted time in column A is by using a Date format for D1. This may be of importance if your workbook is to be used on other computers.

[Leith Ross]

Hello keyston,

I don't have 2007 just the converter to read the files. Is your problem occurring on Excel 2007 then?

[keyston]

Yes, I am using Excel 2007.

One thing that has become clear is that the .Value of a cell is more than the numeric value: it can contain information regarding the formatting of the value, and this formatting information is checked, along with the numeric value, by .Find. Both have to match.

If I enter .1234567 in D1 and format it as a Date, and I then do Range("D2").Value = Range("D1").Value, the format of D2 changes from General to Custom h:mm:ss AM/PM and I see: 2:57:47 AM. This formatting information allows the .Find method to match a Time formatted cell (but only those that are similar, not all Time formats).

However, if I do the same thing with D1 formatted as Time, the format in D2 stays as General (assuming that it was set to General to begin with) and I see in D2: 0.1234567, i.e. for some strange(?) reason .Value does not in this case include any information regarding the Time format, so .Find will not match it to a Time formatted cell.

Then it hit me. Instead of formatting D1 as a Date, I should be able to use your code with one minor modification:

Please Login or Register  to view this content.

This way the match should be independent of the actual format of D1. This worked!! I can use either xlValues or xlFormulas, and the format of D1 can be General, Time, Date etc.

[jeffreybrown]

Hi keyston,

I can see what you are saying, but you would have to take a gamble on how the spreadsheet is formatted.

Using the help from Leith here is what I have come up with for what I needed. I needed to find the time closest to 11:00:00 without exceeding that hour and then place it next to the time. In this case B3.

I also built in the fomat of the cells so I can control picking up a match.

Not saying this is right, but got it to work for my needs now.

Let me know what you think.

[keyston]

Hi Jeff,

The important thing is to get something that works and is reliable. However, given the new information about your project, and some more thought, I would be tempted to use something a bit simpler and more compact like:

Please Login or Register  to view this content.

This works for me, whatever the format of the cells in column A.

[jeffreybrown]

Hi keyston,

Very nice. Thanks for the feeback.

I was thinking about how to keep the time check within the macro, but honestly, I was lost.