+ Reply to Thread
Results 1 to 12 of 12

Find time value in VBA

Hybrid View

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Find time value in VBA

    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.

    Set Found = Columns("A").Find(what:=Range("D1").Value, LookIn:=xlValues, LookAt:=xlWhole)
    Found.Address
    Any thoughts?
    Last edited by jeffreybrown; 05-18-2011 at 01:51 PM.
    HTH
    Regards, Jeff

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Find time value in VBA

    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?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    04-30-2011
    Location
    Halifax, NS
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: 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.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Find time value in VBA

    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.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Find time value in VBA

    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.
    Sub FindTest()
    
      Dim FoundIt As Range
      Dim Rng As Range
      Dim X As Variant
      
        Set Rng = Range("A1:B15")
        X = Range("D1").Value
        
        Set FoundIt = Rng.Find(X, , xlFormulas, xlPart, xlByColumns, xlNext, False, False)
        If Not FoundIt Is Nothing Then MsgBox "Found match in cell " & FoundIt.Address
        
    End Sub

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Find time value in VBA

    Hi Leith,

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

  7. #7
    Registered User
    Join Date
    04-30-2011
    Location
    Halifax, NS
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Find time value in VBA

    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.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Find time value in VBA

    Hello keyston,

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

  9. #9
    Registered User
    Join Date
    04-30-2011
    Location
    Halifax, NS
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Find time value in VBA

    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.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Find time value in VBA

    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.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-30-2011
    Location
    Halifax, NS
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Find time value in VBA

    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.

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Find time value in VBA

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1