Macro to locate records in Tab A and paste corresponding value in Tab B

[sncb]

Hi All,

Im working on 2 tabs where I need the macro code to locate records on Tab 'Ending' and paste the 3rd value beside the corresponding record in Tab 'Run'

Eg:

The macro must search for 'ST' AND '12345' in Tab 'Ending'

Excel Workbook
	A	B	C
1	PO Type	PO Number	Sales Person
2	ST	12345	James
3	ST	45677	Tony
4	UN	22222	Franz
Tab 'Ending'

and in Tab 'Run' must find the corresponding 'ST' and '12345' and paste James in cell C2

Excel Workbook
	A	B	C
1	PO Type	PO Number	Sales Person
2	ST	12345	*
3	ST	45677	*
4	UN	22222	*
Tab 'Run'

Thanks for anyone who can assist.

B Regards

 

[sncb]

I was just thinking and its the other way around.

So if in Tab 'Run' the records 'ST' and '12345' exists then it must look for the corresponding combination in Tab 'Ending' and copy the corresponding value, (James in this case) and paste it in C2 in Tab 'Run'

Thanks for helping.

 

[sncb]

BUMP!! Anyone?

 

[jreedich]

Do you want an iterative process to do the whole table or do you want it to do ONE at a time?

 

[jreedich]

Let me know if this is what you are looking for.

Sub gpFindPoNum()
Dim pvRow As Integer
Dim pvSrchStr As String
Dim pvSalesPerson As String

Application.Goto Sheets("ENDING").Range("Ending_Po_Num")
pvRow = ActiveCell.Row + 1
Do Until Cells(pvRow, 2) = ""
pvSrchStr = Cells(pvRow, 2)
pvSalesPerson = Cells(pvRow, 3)
Sheets("RUN").Select
Cells.Find(What:=pvSrchStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells(ActiveCell.Row, 3) = pvSalesPerson
Sheets("ENDING").Select
pvRow = pvRow + 1
Loop
End Sub

 

[sncb]

Hi,

Thanks for your help and reply.

However Im not sure exactly what you mean by iterative or one at a time. Maybe this will explain better.

There are actually 4 tabs (3 where I copy from and 1 where I copy to). The three are 'Starting', 'Ending' and 'Middle' tabs and the tab where I copy to is 'Run'

At the end of the week, i need to compile all data from these 3 tabs into the 4th one. But there are around 400-500 rows and cannot copy the data from 3 tabs for 400 PO types and Numbers.

For eg:

From Tab 'Starting' the macro needs to get the Sales Person name

Excel Workbook
	A	B	C
1	PO Type	PO Number	Sales Person
2	ST	12345	James
3	ST	45677	Tony
4	UN	22222	Franz
Starting

From Tab 'Middle' the macro needs to get the Order date

Excel Workbook
	A	B	C
1	PO Type	PO Number	Order Date
2	ST	12345	11/2/2011
3	ST	45677	15/02/11
4	UN	22222	19/02/11
Middle

From Tab 'Ending' the macro needs to get the Country name

Excel Workbook
	A	B	C
1	PO Type	PO Number	Country
2	ST	12345	DE
3	ST	45677	DE
4	UN	22222	FR
Ending

And paste all the corresponding data into the 3 columns in Tab 'Run' as below:

Excel Workbook
	A	B	C	D	E
1	PO Type	PO Number	Sales Person	Order Date	Country
2	ST	12345	James	11/2/2011	DE
3	ST	45677	Tony	15/02/11	DE
4	UN	22222	Franz	19/02/11	FR
Run

Thanks for helping out mate.

 

[jreedich]

I hope this is what you are looking for

Assumptions:
1: 4 sheets as youhave indicated above
2: there are NO blanks in the PO Number column until the end of the list

Sub gpUpDateRun()
Dim pvStrtPoNum As String
Dim pvStrtPoTyp As String
Dim pvStrtSlsPrsn As String
Dim pvOrdrDt As Date
Dim pvCntry As String
Dim pvRow As Integer
Dim pvEndRow As Integer
'Stop the screen from updating
Application.ScreenUpdating = False
'Clear and Set up the Run Sheet
Sheets("Run").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Run").Cells(1, 1) = "Po Type"
Sheets("Run").Cells(1, 2) = "PO Number"
Sheets("Run").Cells(1, 3) = "Sales Person"
Sheets("Run").Cells(1, 4) = "Order Date"
Sheets("Run").Cells(1, 5) = "Country"
pvEndRow = 2
'Go to the fist Po Number on sheet starting
Application.Goto Sheets("Starting").Range("StrtPoNum")
pvRow = ActiveCell.Row + 1
'Do This loop until we encounter a blank in the Po Number Column
Do Until Sheets("Starting").Cells(pvRow, 2) = ""
'Fetch the Type, Po Number, and Sales Person
pvStrtPoTyp = Sheets("Starting").Cells(pvRow, 1)
pvStrtPoNum = Sheets("Starting").Cells(pvRow, 2)
pvStrtSlsPrsn = Sheets("Starting").Cells(pvRow, 3)

'Fetch the order date
Sheets("Middle").Select
Cells.Find(What:=pvStrtPoNum, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
pvOrdrDt = Sheets("Middle").Cells(ActiveCell.Row, 3)

'fetch the country
Sheets("Ending").Select
Cells.Find(What:=pvStrtPoNum, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
pvCntry = Sheets("Ending").Cells(ActiveCell.Row, 3)

'Place current data to the run sheet
Sheets("Run").Select
Sheets("Run").Cells(pvEndRow, 1) = pvStrtPoTyp
Sheets("Run").Cells(pvEndRow, 2) = pvStrtPoNum
Sheets("Run").Cells(pvEndRow, 3) = pvStrtSlsPrsn
Sheets("Run").Cells(pvEndRow, 4) = pvOrdrDt
Sheets("Run").Cells(pvEndRow, 5) = pvCntry
'Increment our offsets
pvRow = pvRow + 1
pvEndRow = pvEndRow + 1
Loop
'Done
'Enable screen
Application.ScreenUpdating = False
End Sub

 

[shg]

How about just a formula?

In C2 and copy down,

=INDEX(Ending!$C$1:$C$100, MATCH(Run!A2 & Run!B2, Ending!$A$1:$A$100 & Ending!$B$1:$B$100, 0))

The formula MUST be confirmed with Ctrl+Shift+Enter, not just Enter.

 

[jreedich]

That will work too. He asked for a Macro.

By the way my last line of code is wrong. It should be
Application.ScreenUpdating = true

 

[tusharm]

No need for code, though you may want to consider code to add the formula below.

In C2 of the Run tab enter the array formula =INDEX(Ending!$C$2:$C$4,MAX((Ending!$A$2:$A$4=Run!A2)*(Ending!$B$2:$B$4=Run!B2)*ROW(Ending!$A$2:$A$4))-ROW(Ending!$A$2)+1)

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

Then, copy C2 as far down column C as you have data in B. The quickest way to do this is to move the cursor over the bottom right corner of C2. The cursor will become a bold +. Double-click.

Hi All,

Im working on 2 tabs where I need the macro code to locate records on Tab 'Ending' and paste the 3rd value beside the corresponding record in Tab 'Run'

Eg:

The macro must search for 'ST' AND '12345' in Tab 'Ending'

Excel Workbook
	A	B	C
1	PO Type	PO Number	Sales Person
2	ST	12345	James
3	ST	45677	Tony
4	UN	22222	Franz
Tab 'Ending'

and in Tab 'Run' must find the corresponding 'ST' and '12345' and paste James in cell C2

Excel Workbook
	A	B	C
1	PO Type	PO Number	Sales Person
2	ST	12345	*
3	ST	45677	*
4	UN	22222	*
Tab 'Run'

Thanks for anyone who can assist.

B Regards