Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup items in an array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup items in an array
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup items in an array
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup items in an array
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup items in an array
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank items in VBA Array? | Excel Programming | |||
Adding Items to an array | Excel Programming | |||
Alphabetizing array items | Excel Programming | |||
Counting items in an array | Excel Programming | |||
Number of Items in an Array. | Excel Programming |