Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help! Almost done...
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(xlCellTypeC onstants) 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 -- Sethaholi ----------------------------------------------------------------------- Sethaholic's Profile: http://www.excelforum.com/member.php...fo&userid=2511 View this thread: http://www.excelforum.com/showthread.php?threadid=38872 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|