vlookup items in an array

[Spike]

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

[RB Smissaert]

I don't think there is any problem there with circular references or
whatever.
Just an example to show you it will work.

Sub test()

Dim i As Long
Dim c As Long
Dim arr1(1 To 6) As Long
Dim arr2(1 To 10, 1 To 2) As Long

For i = 1 To 6
arr1(i) = i
Next

For i = 1 To 10
For c = 1 To 2
arr2(i, c) = i + c - 1
Next
Next

'just to see the lookup array
Range(Cells(1), Cells(10, 2)) = arr2

MsgBox arr1(3)

arr1(3) = WorksheetFunction.VLookup(arr1(3), _
arr2, _
2, _
True)

MsgBox arr1(3)

End Sub


RBS


"Spike" <Spike@discussions.microsoft.com> wrote in message
news:25C72FEA-66FA-4920-9985-444C49003F6B@microsoft.com...
> 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

[RB Smissaert]

The other thing to mention here is that just looping through the array is
much faster than doing a VLookup.
Look at this simplified example, just paste the whole lot in a normal
module, making sure the top 4 lines come at the top of
the module. Then run the Sub speedtest.

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

r = 60000
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

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

End Sub

And this loop is still very inefficient. It probably will be much faster
with a binary search.
Just to keep in mind if you are dealing with large arrays.

RBS


"Spike" <Spike@discussions.microsoft.com> wrote in message
news:25C72FEA-66FA-4920-9985-444C49003F6B@microsoft.com...
> 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

[RB Smissaert]

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@discussions.microsoft.com> wrote in message
news:25C72FEA-66FA-4920-9985-444C49003F6B@microsoft.com...
> 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

[Spike]

Thank you very much that looks grand to me, very much appreciated
--
with kind regards

Spike


"RB Smissaert" wrote:

> I don't think there is any problem there with circular references or
> whatever.
> Just an example to show you it will work.
>
> Sub test()
>
> Dim i As Long
> Dim c As Long
> Dim arr1(1 To 6) As Long
> Dim arr2(1 To 10, 1 To 2) As Long
>
> For i = 1 To 6
> arr1(i) = i
> Next
>
> For i = 1 To 10
> For c = 1 To 2
> arr2(i, c) = i + c - 1
> Next
> Next
>
> 'just to see the lookup array
> Range(Cells(1), Cells(10, 2)) = arr2
>
> MsgBox arr1(3)
>
> arr1(3) = WorksheetFunction.VLookup(arr1(3), _
> arr2, _
> 2, _
> True)
>
> MsgBox arr1(3)
>
> End Sub
>
>
> RBS
>
>
> "Spike" <Spike@discussions.microsoft.com> wrote in message
> news:25C72FEA-66FA-4920-9985-444C49003F6B@microsoft.com...
> > 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
>
>