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" wrote in message
...
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