I'm having a problem with a binary search of three databases. I'm trying to compare 86,000+ strings in one database against two other databases, each with over 86,000 entries as well. When I load the other two into arrays, then perform a binary search of the two arrays, I get 274 "hits" as being missing as compared to the master. But if I search the the same two databases using a FIND function (which takes an hour to run) I only get 85 hits. The two arrays are sorted, which I've double checked with a simple =if(a2<a3.... formula. I just can't figure it out. My binary search is below
------------------- CODE ----------------------
Function BinarySearch(strArray() As String, strSearch As String) As Long
Dim lngIndex As Long
Dim lngFirst As Long
Dim lngLast As Long
Dim lngMiddle As Long
Dim bolInverseOrder As Boolean
lngFirst = LBound(strArray)
lngLast = UBound(strArray)
bolInverseOrder = (strArray(lngFirst) > strArray(lngLast))
BinarySearch = lngFirst - 1
Do
lngMiddle = (lngFirst + lngLast) \ 2
If strArray(lngMiddle) = strSearch Then
BinarySearch = lngMiddle
Exit Do
ElseIf ((strArray(lngMiddle) < strSearch) Xor bolInverseOrder) Then
lngFirst = lngMiddle + 1
Else
lngLast = lngMiddle - 1
End If
Loop Until lngFirst > lngLast
End Function
------------------- CODE ----------------------
I'll email the file to the moderator so they can post it in the files section. Thanks for your help.
Scott B)
moderator edit:
The file, called BinarySearchProblem.xlsm has been added to the
Files\Files needing help
section.
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar