Requirements:

First it needs to search in column D for a numeric number (personnel
number) and look it up in a list from sheet 1 (contains name and
personnel number) and once it find a matching personnel number. It
will copy and paste the personnel number in column A while the name
will be paste in column B. When it encounters a blank cell it will
jump to another row in column D and look for a personnel number again.
Once it jump for the 15th time and not yet find a personnel number, it
will now stop.


--
japorms
------------------------------------------------------------------------
japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
View this thread: http://www.excelforum.com/showthread...hreadid=468141

You can probably do this by using the Vlookup worksheet function without
having to use any code. If the personnel number in sheet1 is not in column
A, then you can use a combination of Index and match

=Index(sheet1!A:A,Match(d3,Sheet1!B:B,0),1)

--
Regards,
Tom Ogilvy


"japorms" wrote in
message ...

Requirements:

First it needs to search in column D for a numeric number (personnel
number) and look it up in a list from sheet 1 (contains name and
personnel number) and once it find a matching personnel number. It
will copy and paste the personnel number in column A while the name
will be paste in column B. When it encounters a blank cell it will
jump to another row in column D and look for a personnel number again.
Once it jump for the 15th time and not yet find a personnel number, it
will now stop.


--
japorms
------------------------------------------------------------------------
japorms's Profile:
http://www.excelforum.com/member.php...fo&userid=6544
View this thread: http://www.excelforum.com/showthread...hreadid=468141

japorms,

Enter this in cell A2:
=IF(COUNT($A$1:A1)<15,IF(ISNUMBER(D2),D2,""),"")
And a formula like this in cell B2 (this assumes your numbers are in column A and the names in
column B - you may need to change the Vlookup range or change the vlookup to a combo if index and
match, depending on your layout of sheet1):
=IF(A2<"",VLOOKUP(A2,Sheet1!A:B,2,FALSE),"")

Copy them both down column A to match your data in column D.

HTH,
Bernie
MS Excel MVP


"japorms" wrote in message
...

Requirements:

First it needs to search in column D for a numeric number (personnel
number) and look it up in a list from sheet 1 (contains name and
personnel number) and once it find a matching personnel number. It
will copy and paste the personnel number in column A while the name
will be paste in column B. When it encounters a blank cell it will
jump to another row in column D and look for a personnel number again.
Once it jump for the 15th time and not yet find a personnel number, it
will now stop.


--
japorms
------------------------------------------------------------------------
japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
View this thread: http://www.excelforum.com/showthread...hreadid=468141