Find time value in VBA

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.

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.

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:

 Change:
Dim X as Variant
to 
Dim X as Date

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.

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:

Sub FindTime2()
    Const DataColumn As String = "A"
    Dim RowNum As Integer
    Dim t1 As Date
    t1 = TimeSerial(11, 0, 0)
    For RowNum = 1 To Range(DataColumn & Rows.Count).End(xlUp).Row
        If CDate(Range(DataColumn & RowNum).Value) > t1 Then
            Range(DataColumn & RowNum).Offset(-1, 1) = t1
            Exit For
        End If
    Next
    Range(DataColumn & (RowNum - 1)).Select

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