Find value closest to a certain time with VBA

[Christian_Ku]

Hello,

I have a column with time values that differ approximately 30s of each other (e.g. 00:01:34, 00:02:04, etc.). The column next to it contains data measured at that time. Now I only need the data until, say, 12:00:00, however most of the time this exact value does not exist, it is either 11:59:47 or 12:00:13.

So when I try to use the find function I usually get the 00:01:12 or somthing like that, if I use "12:" I get 00:12:33 or something like that. In any case with the find function used in this fashion I will never get 12:00:00 or the value closest to it in the way I'm using it now. I've searched this site over and over but I could not find anything and the excel 2003 help file is completely useless when one uses the search function. Hopefully it can be done (i think it can). Thanks

 

[Weaver]

Try

=VLOOKUP(0.5,$A$1:$A$42,1,1)

where $A$1:$A$42 is your range of times

 

[Christian_Ku]

Thanks, that works ofcourse but I need this to work in VBA code, or in other words in my macro, I want to select the data in the column next to the time column from 12:00:00 until the end (which is in this case always 00:00:00).

 

[Weaver]

try this

Sub test()
    getClosestTime 0.5, Range("A1:A42")
End Sub
 
--------------------------------------------------------
 
Sub getClosestTime(ByVal target As Date, searchtime As Range)
    Dim diff As Double
    diff = 1
    For Each tm In searchtime
        newdiff = Abs(target - tm)
        If newdiff < diff Then
            diff = newdiff
            tm.Select
        End If
    Next tm
End Sub

 

[Weaver]

tm.row will give you your start row, then you could use end(xldown) to get your last row.

I wrote the above code as an experiment and it doesn't quite do what you wanted, but if you can't see how to adapt it, let me know and I'll finish it, but I'm off my lunch now so I might not be able to do it straight away.

This might help

Sub test()
    Debug.Print getClosestTime(0.5, Range("A1:A42"))
End Sub
 
_________________________________
 
Function getClosestTime(ByVal target As Date, searchtime As Range)
    Dim diff As Double
    Dim fr As Integer
    diff = 1
    For Each tm In searchtime
        newdiff = Abs(target - tm)
        If newdiff < diff Then
            diff = newdiff
            fr = tm.Row
        End If
    Next tm
    getClosestTime = fr
End Function

 

[Christian_Ku]

After some fumbling a long a got almost the same macro as your second, however yours looks much cleaner so use yours right now. However my fumbling was ofcourse not entirely useless, because now I know what is happening, although I think it strange that there has to be such a 'complex' system to do such thing.

Thanks a bunch though!

 

[Christian_Ku]

Wait, I lied, I don't entirely understand what the ByVal does? And another question, I was wondering if it was possible to use a time integer, e.g. 12:00:00. I have been experimenting with this start:

Sub test()

Dim ttime As Date

ntime = #11:59:59 PM#
ttime = #12:00:00 PM#
ftime = ttime / ntime

End Sub

which would give the number of 0.5 in your function. I've remodelled it now like this:

Function getClosestTime(ttime as Date, searchtime As Range)
    Dim diff As Double
Dim ntime as Date
    diff = 1
ntime = #11:59:59 PM#
target = ttime/ntime
    For Each tm In searchtime
        newdiff = Abs(target - tm)
        If newdiff < diff Then
            diff = newdiff
            fr = tm.Row
        End If
    Next tm
    getClosestTime = fr
End Function

This seems to work, but maybe there is a more clean solution?

 

[Christian_Ku]

Ok, changed the function again, ofcourse when I enter a time for the target I don't need to calculate the value as it is already done by

newdiff = Abs(target - tm)

looks now like this

Function getClosestTime(target As Date, searchtime As Range)
    Dim diff As Double
    diff = 1
    For Each tm In searchtime
        newdiff = Abs(target - tm)
        If newdiff < diff Then
            diff = newdiff
            fr = tm.Row
        End If
    Next tm
    getClosestTime = fr
End Function

it seems I only removed ByVal, I really have no clue why?

 

[Weaver]

All the byval does is help define the data types used. I don't exactly know why it's used or why it works without it, it's just a habit, I guess.

Me using 0.5 for midday was plain lazy, but there you go. I was really hoping a function would solve it, cos too many times on this site, I've sat & wrote code only to be upstaged by a really neat usage of a built in function!

Hence the sig...