Re: vlookup items in an array
- From: "RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx>
- Date: Wed, 8 Mar 2006 23:07:52 -0000
The other thing is that VLookup won't work with arrays of more than 65336 rows.
Just to show that a binary search is indeed much faster (but the array will hvae to be sorted) this code:
Option Explicit
Private lStartTime As Long
Private lEndTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub StartSW()
lStartTime = timeGetTime()
End Sub
Sub StopSW(Optional ByRef strMessage As Variant = "")
lEndTime = timeGetTime()
MsgBox "Done in " & lEndTime - lStartTime & " msecs", , strMessage
End Sub
Sub speedtest()
Dim i As Long
Dim c As Long
Dim r As Long
Dim lValue As Long
Dim arr2() As Long
On Error GoTo ERROROUT
r = 65536
lValue = 30000
ReDim arr2(1 To r, 1 To 2) As Long
For i = 1 To r
For c = 1 To 2
arr2(i, c) = i + c - 1
Next
Next
StartSW
lValue = arr2(BinarySearchLong(lValue, 1, arr2), 2)
StopSW lValue
lValue = 30000
StartSW
For i = 1 To r
If arr2(i, 1) = lValue Then
lValue = arr2(i, 2)
Exit For
End If
Next
StopSW lValue
lValue = 30000
StartSW
lValue = WorksheetFunction.VLookup(lValue, _
arr2, _
2, _
True)
StopSW lValue
Exit Sub
ERROROUT:
MsgBox Err.Description & vbCrLf & _
"Error number: " & Err.Number, , ""
End Sub
Function BinarySearchLong(ByVal lLookFor As Long, _
ByVal lSearchCol As Long, _
ByRef lArray As Variant, _
Optional ByVal lNotFound As Long = -1) As Long
Dim lLow As Long
Dim lMid As Long
Dim lHigh As Long
On Error GoTo ERROROUT
'Assume we didn't find it
BinarySearchLong = lNotFound
'Get the starting positions
lLow = LBound(lArray)
lHigh = UBound(lArray)
Do
'Find the midpoint of the array
lMid = (lLow + lHigh) \ 2
If lArray(lMid, lSearchCol) = lLookFor Then
'We found it, so return the location and quit
BinarySearchLong = lMid
Exit Do
Else
If lArray(lMid, lSearchCol) > lLookFor Then
'The midpoint item is bigger than us - throw away the top half
lHigh = lMid - 1
Else
'The midpoint item is smaller than us - throw away the bottom half
lLow = lMid + 1
End If
End If
'Continue until our pointers cross
Loop Until lLow > lHigh
ERROROUT:
End Function
RBS
"Spike" <Spike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:25C72FEA-66FA-4920-9985-444C49003F6B@xxxxxxxxxxxxxxxx
i have a long list of codes in one column and would like to convert them to
new codes by looking them up in a separate worksheet. As i am pushed for
memory i thought i would change to the new codes by putting the old codes
into an array, then looking up each item in the array and then placing the
new codes back on the worksheet in place of old code that was originally
looked up. The problem as i see it is that it is a sort of circular
reference so i do not think it will work. I can get the items into and out
of the array no problem, it is just the code for the one line part when the
element of the array looks itself up using a vlookup that i would be grateful
for, if it will indeed work! something along the following lines
NewCodes(intA) = vlookup(NewCodes(intA), variable holding spreadsheet range
etc, 2,false)
Any ideas will be very gratefully received.
with kind regards
Spike
.
- Prev by Date: Re: What are the benefits of using VB for excel formulas?
- Next by Date: Re: If This AND That Then...Else... in VBA
- Previous by thread: Re: vlookup items in an array
- Next by thread: Re: How do you automate invoice numbers?
- Index(es):
Relevant Pages
|
