Hi all,

In sheet A in col AU are more than 600 numers, say 344, 2368 etc. In col BI
of sheet A are corresponding amounts (numbers or nothing, no text, no
formulas). Sheet B in col. M also has some numbers in it (numbers or
nothing, no text, no formulas) which all occur in col AU of sheet A.

I need efficient (fast) code to copy the corresponding amount in col BI of
sheet A to col N in sheet B, say from row 2 (in sheet B) to row 60.

The result (the amount in col N of sheet B) should be like this:

sheet A sheet B

col AU col BI col M col N

row 345 2368 240 row 44 2368 240

I use XP SP2 and Excel 2K. Thanks in advance for your assistance.

Jack Sons
The Netherlands

If the key numbers in SheetA!AU:AU are not repeated, then you need
VLOOKUP().

In SheetB!N2:
=VLOOKUP(M2, SheetA!AU:BI, 15, false)

HTH
Kostis Vezerides

Kostis,

I'll try to be more clear.

The numbers in col M of sheet B are also somewhere in col AU of sheet A. In
the same row as the number in col AU of sheet A, but in col BI, is a number
that I need in col N in sheet B, in the same row as the number that is the
basis for this all. I know it could be done with vlookup, but in this case I
can't use a worksheet function. Not even by letting VBA put it in cells of
col N of sheet B.

So VBA has to do the work of vlookup. What is fast code to do that job?

Jack.




"vezerid" schreef in bericht
oups.com...
If the key numbers in SheetA!AU:AU are not repeated, then you need
VLOOKUP().

In SheetB!N2:
=VLOOKUP(M2, SheetA!AU:BI, 15, false)

HTH
Kostis Vezerides

Jack,
Of course you can always use formula and then Copy|Paste Special... |
Values, to eliminate any trace of formulas. Still, this is a search
problem where the source data is not ordered and as such it would
require rather elaborate data structures and algorithms to solve in
less than O(m*n), where m is the number of rows in SheetA and n is the
number of rows in your target sheet. VLOOKUP is itself efficiently
written. So the best I can come up with is using VLOOKUP from VBA.

Sub MyVlookup()
dim sh1 as worksheet, sh2 as worksheet
Set sh1 = thisworkbook.sheets("SheetA")
Set sh2 = thisworkbook.sheets("SheetB")

i=2
while sh2.cells(i,13)<""
sh2.cells(i,14) =
application.worksheetfunction.vlookup(cells(i,13),
sh2.range("AU:BI"),15,false)
i=i+1
wend
end sub

HTH
Kostis