Searching for Nearest Next DateTime in VBA

A

alexcn

Hi Everyone,

I am trying to find the nearest datetime after a range of values in a
lookup table using Excel VBA. I have tried the various
WorksheetFunction.Match and WorksheetFunction.V[H]Lookup functions but
these (for "nearest" values" depend on the searchable data being
sorted, which I cant guarantee. I have also tried the various
Range.Find and Cells.Find methods again to no avail. I have tried
converting the datetime to serial format, mm/dd/yyyy hh:mm:ss both
before and after passing the value to a UDF but still it returns
nothing. I realise this is something a lot of people have trouble
with, but was wondering if anyone had any pointers. My data range is
as follows, sorted for the purpose of this post but by definition it
wont be sorted in real life:

DateTime
23/03/2010 15:22
23/03/2010 15:58
23/03/2010 16:04
23/03/2010 16:17
23/03/2010 16:23
23/03/2010 16:37
23/03/2010 16:51
23/03/2010 17:01
23/03/2010 17:08
23/03/2010 20:38

Thanks to anyone that can offer some assistance,

Alex
 
J

Jim Cone

What is the value you use in your search?
What is the data type of the value?
I assume your lookup table in an Excel sheet - is the table text or numbers?

Why not sort the data, do the lookup and then return the list to the original order?
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"alexcn" <[email protected]>
wrote in message
Hi Everyone,

I am trying to find the nearest datetime after a range of values in a
lookup table using Excel VBA. I have tried the various
WorksheetFunction.Match and WorksheetFunction.V[H]Lookup functions but
these (for "nearest" values" depend on the searchable data being
sorted, which I cant guarantee. I have also tried the various
Range.Find and Cells.Find methods again to no avail. I have tried
converting the datetime to serial format, mm/dd/yyyy hh:mm:ss both
before and after passing the value to a UDF but still it returns
nothing. I realise this is something a lot of people have trouble
with, but was wondering if anyone had any pointers. My data range is
as follows, sorted for the purpose of this post but by definition it
wont be sorted in real life:

DateTime
23/03/2010 15:22
23/03/2010 15:58
23/03/2010 16:04
23/03/2010 16:17
23/03/2010 16:23
23/03/2010 16:37
23/03/2010 16:51
23/03/2010 17:01
23/03/2010 17:08
23/03/2010 20:38

Thanks to anyone that can offer some assistance,
Alex
 
A

alexcn

Thanks for your reply Jim,

In terms of the points raised:
1) The source dates are displayed as Date Time, but stored as serial
dates.
2) The same goes for the destination dates, again stored as serial
dates.
3) I pass the source into a UDF using datatype Date, the destination
is passed into the UDF as a range using datatype Variant.
4) As I mentioned, I have tried converting the passed date as CDbl and
CDate, and also using DateValue, and lastly using Format(<date>,"mm/dd/
yyyy hh:mm:ss)
5) Yes both sides are in Excel, I cant guarantee sorting the data, and
also it would need to be dynamically sorted for each pass. There
could be 100k's of destination (search) rows.
6) The following is the function as it currently stands:

Function funcTradeExtend( _
datExitDateTime As Date, _
rngMarketData As Variant)

On Error Resume Next
intRow = Range(rngMarketData).Find(What:=datExitDateTime,
LookIn:=xlFormulas, Lookat:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext)
funcTradeExtend = rngMarketData.Cells(intRow)
End Function

Regards and thanks again,

Alex
 
A

alexcn

I would need to sort the incoming range in VBA perhaps if I was to
follow your suggestion, but this would happen on each pass so given
the size of the dataset it would have a huge overhead as I dont
believe a sort array function exists in Excel. Incidentally I am
using Excel 2007.
 
J

Jim Cone

Some observations and comments...

1. Find does not return "close". It returns exact or nothing and it
returns a range object not a numeric value.
You would need something like...
Set rngFound = Range(rngMarketData).Find(What:=datExitDateTime, ...
If Not rngFound is Nothing Then
funcTradeExtend = rngFound.Value
Else
'make apologies
End If

2. intRow should be a Long. An integer declaration is only valid up to ~ 32000.
3. Do not use On Error Resume Next when testing code.

4. An Excel worksheet sort is about as fast as it gets.
Fill an adjacent column with consecutive numbers.
The number column is included in the sort
Sort, then find your item
Sort again using the numbers column.
Clear or delete the numbers column.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html

..
..
..

"alexcn" <[email protected]>
wrote in message
Thanks for your reply Jim,

In terms of the points raised:
1) The source dates are displayed as Date Time, but stored as serial
dates.
2) The same goes for the destination dates, again stored as serial
dates.
3) I pass the source into a UDF using datatype Date, the destination
is passed into the UDF as a range using datatype Variant.
4) As I mentioned, I have tried converting the passed date as CDbl and
CDate, and also using DateValue, and lastly using Format(<date>,"mm/dd/
yyyy hh:mm:ss)
5) Yes both sides are in Excel, I cant guarantee sorting the data, and
also it would need to be dynamically sorted for each pass. There
could be 100k's of destination (search) rows.
6) The following is the function as it currently stands:

Function funcTradeExtend( _
datExitDateTime As Date, _
rngMarketData As Variant)

On Error Resume Next
intRow = Range(rngMarketData).Find(What:=datExitDateTime,
LookIn:=xlFormulas, Lookat:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext)
funcTradeExtend = rngMarketData.Cells(intRow)
End Function

Regards and thanks again,

Alex

What is the value you use in your search?
What is the data type of the value?
I assume your lookup table in an Excel sheet - is the table text or numbers?

Why not sort the data, do the lookup and then return the list to the original order?
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware

.
.
.

"alexcn" <[email protected]>
wrote in message
Hi Everyone,

I am trying to find the nearest datetime after a range of values in a
lookup table using Excel VBA. I have tried the various
WorksheetFunction.Match and WorksheetFunction.V[H]Lookup functions but
these (for "nearest" values" depend on the searchable data being
sorted, which I cant guarantee. I have also tried the various
Range.Find and Cells.Find methods again to no avail. I have tried
converting the datetime to serial format, mm/dd/yyyy hh:mm:ss both
before and after passing the value to a UDF but still it returns
nothing. I realise this is something a lot of people have trouble
with, but was wondering if anyone had any pointers. My data range is
as follows, sorted for the purpose of this post but by definition it
wont be sorted in real life:

DateTime
23/03/2010 15:22
23/03/2010 15:58
23/03/2010 16:04
23/03/2010 16:17
23/03/2010 16:23
23/03/2010 16:37
23/03/2010 16:51
23/03/2010 17:01
23/03/2010 17:08
23/03/2010 20:38

Thanks to anyone that can offer some assistance,
Alex
Click to expand...
 
A

alexcn

Thanks Jim,

Hmm I didnt realise Find wouldnt find a fuzzy match, in which case
that approach isnt going to work as I could safely say that there
would be an exact match (owing to the data going down into "second"
increments) on about 98% of the search passes. Asides from having to
sort the data (and thanks Ron to you as well for your input) can you
think of looking for the nearest matched date in unsorted data?

Thanks,

Alex
 
A

alexcn

Further conundrum, before working on the sort bit in VBA I thought I
would try something, and it doesnt work either, no error just bypasses
the statement:

test = WorksheetFunction.Match(CDbl(datExitDateTime),
Range(rngMarketData).Cells, 1)

Tried with and without converted the date using CDbl()
 
A

alexcn

Thanks Ron,

It worked in itself, but has also prompted some more ideas working
with the "find the smallest difference" notion you suggested: have
adapted to find the difference greater than the searched value, i.e.
the next largest date/time.

Have a great weekend,

Alex
 
A

alexcn

Thanks again Ron,

I havent tried the easier VBA-option using LARGE as yet will try once
I have sent this, but am wondering how you actually code an "array"
formula in VBA or if you actually pass in an array-formula when you
call the UDF from the worksheet?

Alex
 
A

alexcn

Wow, I owe you one Ron, you have given me so many more ideas about how
to approach the problem.

One last question, my data is in an Excel 2007 Table, so would you
recommend processing the data:
1) by passing into the function as a Range, or multiple ranges
2) by hardcoding the range in the function
3) By passing in the whole excel table, determining which columns I
want to use by looking at the column headers (which then allows for
tables in different layouts) but then how do I pass the table so it
works as a Listobject, as most of the forum postings I find involving
invoking it as a subcollection of the activesheet etc

Thanks once again, you have saved me so much time and stress,

Alex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Suming Up Time over the course of days 1
Find then highlight in yellow 6
Date Function 2
Pivot Table Summarizing Quirk - Please Help 1
Copy value according to date 1
Changing constants in a query to user input values 2
sumproduct error 1
Calculation of Overlap Outage Hours 17

Top