<I guess I can pull out my best guess candidate and then sequentially reduce or increase>
Don't bother. Just use a binary search. I tested the one below on a 35000*3 array. One million searches took 7 seconds.
You'll have to change it slightly to incorporate your tolerance.
Or just use the worksheet functiions, as both Bob and I suggested.
I picked this one from a newgroup, but unfortunately can't remember who wrote it.
I changed it for just numbers and a few other minor things, so do test it!
===============================================================
Option Explicit
Function BSearchNumber(vRng(), vItem As Double)
' --------------------------------------------------
' Like BSearch, but only for numbers
' DESCRIPTION: Perform a binary search on an (assumed)
' sorted list
'
' RETURNS: The record/row number in which
' a match was found or #VALUE if no
' match found
' --------------------------------------------------
Dim FirstRec As Long, LastRec As Long
Dim TestRec As Long, LastTest As Long
Dim VarFound As Double
LastTest = -1
FirstRec = 1
LastRec = UBound(vRng)
Do
TestRec = Int((FirstRec + LastRec + 1) / 2)
If TestRec = LastTest Then Exit Do
LastTest = TestRec
VarFound = vRng(TestRec, 1)
If VarFound = vItem Then
BSearchNumber = TestRec
Exit Function
End If
If VarFound < vItem Then
FirstRec = TestRec
Else
LastRec = TestRec
End If
Loop
' If here then value not found
BSearchNumber = CVErr(xlErrValue)
End Function
===============================================================
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Dear Niek, dear Bob,
| ... which begs the next question: how do I (efficently) search in my newly
| created arrays, preferably using an application.match or vba find type of
| function (the examples have all search through ranges). I guess I can pull
| out my best guess candidate and then sequentially reduce or increase) the
| element position position unit I get to the right one but is there a built in
| function for thsi?
| Thanks again to both of you for your very helpful comments.
| Best wishes, Boris.
|
| "Niek Otten" wrote:
|
| > < Are you aware of a good way of reading in even such large data sets?>
| >
| > The way Bob suggested; doing that 100 times took 3 seconds
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Hi Niek,
| > | Thanks for your very fast reply.
| > | My data set is typically 35000 rows and at least three columns. I am rather
| > | wary of reading that as an array in to VBA though I am well aware that VBA is
| > | much faster at accessing internally stored data. Are you aware of a good way
| > | of reading in even such large data sets? If not, I guess could perhaps do it
| > | in junks...
| > | I will try Application.Match (though I had read that Find is up to 100 times
| > | faster) but am surpirsed that there is no vba built in method for approximate
| > | finds (I have been trying to achieve the desired result by playing around
| > | with the date formatting and converting the result to a string for the
| > | comparison but...).
| > | Thanks gain for your response - and if you can let me know how to read in
| > | large data sets efficiently, that would be really handy.
| > | Best wishes, Boris.
| > |
| > | "Niek Otten" wrote:
| > |
| > | > Application.Match on a sorted table, approximate search, is very fast. A self developed binary search can be faster, it
seems
| > to
| > | > depend on several parameters and I've seen cases where it was slower. Anyway, read the whole table into a VBA array and
search
| > in
| > | > that array; much faster than looking in the worksheet.
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | >
| > | > | Hi,
| > | > | I am trying to mimic the approximate find methods used in Excel MATCH,
| > | > | VLOOKUP, etc. application functions in VBA. I have a large(!) data set and,
| > | > | in this particular instance, want to perform operations (average, max, min)
| > | > | on subranges containing data for different periods. The data is arranged in
| > | > | ascending order and I am using the date and time column of the data to select
| > | > | my subranges. However, although the data is usually equally spaced, there is
| > | > | a small tolerances (no doubt due to rounding operations) and some date and
| > | > | time values will be missing. As I am performing this action thousands of
| > | > | times in the code, I do not want to resort to Application.VLOOKUP or
| > | > | Application.Match. I cannot, however, find any adjustments to the VBA FIND
| > | > | method that will allow me to check for a match within a certain tolerances
| > | > | (say within five minutes of my target date and time). What I would really
| > | > | like is to find the cell in the column with and exact or largest value that
| > | > | is less than my target (and I can then check the tolerances separately).
| > | > | Although I am sure that this is a common requirement, I have been unable to
| > | > | find anything to allow me to do this in an efficient manner.
| > | > | Any help, clues or suggestions would be most gratefully appreciated.
| > | >
| > | >
| > | >
| >
| >
| >
