Find corresponding record and paste in sheet 2

[kettler]

Hi All,

Good morning to everyone.

I have a sheet 1 which has the following columns.

Excel Workbook
	A	B
1	Sheet 1	*
2	Customer agent	Zone responsible
3	*	*
4	Jim	EU
5	Tony	EU
6	Francis	NA
7	Jason	SA
8	Leslie	CN
9	Jackie	ROW
Sheet1

How can i write a macro for sheet 2 to search in sheet 1 and if it finds a matching record then paste its corresponding value in sheet 2.

Excel Workbook
	D	E
1	Sheet 2	*
2	Customer agent	Zone responsible
3	*	*
4	Jim	*
5	Tony	*
6	Francis	*
7	Jason	*
8	Leslie	*
9	Jackie	*
Sheet2

PS: I dont know why the stars are coming in the blank cells but its not supposed to be there.

 

[VoG]

You should be able to use a VLOOKUP formula. See http://www.contextures.com/xlFunctions02.html

 

[kettler]

Thank you there,

I have read through the article and its excellent for one time use. However my data is changing weekly and my manager wants to press a button instead of copying/cutting/pasting and adjusting (she's very demanding).

so instead of making vlookup im thinking of using a macro. however im not a programing person and donno how to make a macro.

my data is actually 3 columns.

Excel Workbook
	A	B	C
1	Customer agent	Zone responsible	sales
2	Jim	EU	6199
3	Tony	EU	2323
4	Francis	NA	16548
5	Jason	SA	4542
6	Leslie	CN	4656
7	Jackie	ROW	98588
Sheet1

and she wants to have automatic data pasted in sheet 2 in C2 to C7 in this example.

Excel Workbook
	A	B	C
1	Customer agent	Zone responsible	sales
2	Jim	EU
3	Tony	EU
4	Francis	NA
5	Jason	SA
6	Leslie	CN
7	Jackie	ROW
Sheet2

Thanks for assisting.
Good day to all.

 

[VoG]

Try

Sub LOOK()
Dim LR As Long, i As Long
With Sheets("Sheet2")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        .Range("C" & i).Value = Application.VLookup(.Range("A" & i).Value, Sheets("Sheet1").Columns("A:C"), 3, False)
    Next i
End With
End Sub

 

[kettler]

Hi Vogg

When i try the code, I get in some cells #REF! and some I get #N/A

Is it Im doing something erroneous? Like this below.

Excel Workbook
	L
32	#REF!
33	#REF!
34	#REF!
35	#REF!
36	#REF!
37	#REF!
38	#N/A
39	#REF!
40	#REF!
41	#N/A
42	#N/A
Testing

 

[VoG]

My results with your example.

Excel Workbook
	A	B	C
1	Customer agent	Zone responsible	sales
2	Jim	EU	6199
3	Tony	EU	2323
4	Francis	NA	16548
5	Jason	SA	4542
6	Leslie	CN	4656
7	Jackie	ROW	98588
Sheet2

What is the actual code you are using?

 

[kettler]

Hello vogg,

this is the code im using:

"Sub testing()
Dim LR As Long, i As Long
With Sheets(""sheet 2"")
LR = .Range(""L"" & Rows.Count).End(xlUp).Row
For i = 3 To LR
.Range(""L"" & i).Value = Application.VLookup(.Range(""O"" & i).Value, Sheets(""sheet 1"").Columns(""D""), 6, False)
Next i
End With
End Sub"


just to explain to be clear:

source tab:
sheet 1
data common to both sheets: in Column D
source data: column F


destination tab:
sheet 2
data common to both sheets: in Column O
data expected to be pasted in: column L

 

[VoG]

Try

Sub LOOK()
Dim LR As Long, i As Long
With Sheets("Sheet2")
    LR = .Range("O" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        .Range("L" & i).Value = Application.VLookup(.Range("O" & i).Value, Sheets("Sheet1").Columns("D:F"), 3, False)
    Next i
End With
End Sub

 

[kettler]

Hi vogg,

this is fanta-fabulous. thanks to you its working like a charms.

may you have a great day.