Please help! Almost done...

[Sethaholic]

Hi,

I have 2 workbooks.

In the first workbook, it is divided into several worksheets b
person's name. Under each person's name, it has a list of accoun
numbers like this:

5-11111
5-22222
5-12345
etc.

In the second workbook, I have account numbers and correspondin
personnel next to them, like this:


5-11111 John Smith
David Lee
Allan Houston
Nate Robinson

5-22222 Keira Lee
Jamal Crawford

5-12345 Larry Brown
George Bush

etc. etc

How can I code vba to look for the account numbers in workbook 1 an
match them with the numbers in workbook2. If they match, then copy an
paste the information next to it into workbook1 next to the accoun
number. When pasting this information, it should shift the rows down s
that it can be in the format of the 2nd workbook. Also, if there is n
matching account number, then return "N/A"


Here is my code so far...



Sub GetPersonnel()

Dim intRec As Integer, rngData As Range, rngItem As Range
rngPersonnel As Range, rngOut As Range
Dim mysht As Worksheet

Application.ScreenUpdating = False

For Each mysht In ThisWorkbook.Worksheets
With mysht
Set rngData = .Range("A70"
.Range("A500").End(xlUp)).SpecialCells(xlCellTypeConstants)
End With
With Workbooks("Intermediary - PWC").Worksheets("sheet3")
Set rngPersonnel = .Range("A1:A"
.Range("A65536").End(xlUp).Row)

End With

For Each rngItem In rngPersonnel
Set rngOut = rngData.Find(What:=rngItem)

If Not rngOut Is Nothing Then
rngOut.Offset(0, 2).Value = rngItem.Offset(0, 1).Value
rngOut.Offset(0, 4).Value = rngItem.Offset(0, 2).Value
rngOut.Offset(1, 2).Value = rngItem.Offset(1, 1).Value
rngOut.Offset(1, 4).Value = rngItem.Offset(1, 2).Value
rngOut.Offset(2, 2).Value = rngItem.Offset(2, 1).Value
rngOut.Offset(2, 4).Value = rngItem.Offset(2, 2).Value
Else
End If

Next rngItem
Next mysht
End Sub


It's not very good because there are sometimes more than 3 personne
for each account. How can I make it so that it would continue searchin
for personnel names until the cell is blank, and once it is blank, the
stop and go to the next account number. Please please please help
Thanks in advance