Find value closest to a certain time with VBA

Christian_Ku

Board Regular
Joined
Sep 10, 2007
Messages
102
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Try

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

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

Christian_Ku

Board Regular
Joined
Sep 10, 2007
Messages
102
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
try this

Code:
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

Code:
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

Board Regular
Joined
Sep 10, 2007
Messages
102
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

Board Regular
Joined
Sep 10, 2007
Messages
102
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:

Code:
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:

Code:
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

Board Regular
Joined
Sep 10, 2007
Messages
102
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

Code:
newdiff = Abs(target - tm)
looks now like this
Code:
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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...
 

Forum statistics

Threads
1,082,646
Messages
5,366,737
Members
400,917
Latest member
BlueBeerR

Some videos you may like

This Week's Hot Topics

Top