Find Value One Sheet to another sheet & Update value

[smartguy]

Hello all,

I have excel file in the below format.

Excel Workbook
	B	C	D	E	F
5	Name	Class	Value	Blood group	Targe
6	A	12
7	B	12
8	C	10
9	D	12
10	F	12
11	G	12
12	H	12
13	I	12
14	J	12
15	A	12
Sheet1

I want to get data base on sheet 2.( Column "A" & "E")

Excel Workbook
	A	B	C	D	E	F
1	Name	Value	Blood group	Targe	Class	exam
2	A	1	2	3	12	no
3	B	sd	ff	f	12	df
Sheet2

Answer :

Excel Workbook
	B	C	D	E	F
5	Name	Class	Value	Blood group	Targe
6	A	12	1	2	3
7	B	12	sd	ff	f
8	C	10
9	D	12
10	F	12
11	G	12
12	H	12
13	I	12
14	J	12
15	A	12	1	2	3
Sheet1

Please help to Provide VBA Code....

 

[JackDanIce]

Have you tried to record a macro to see what code it suggests?

 

[smartguy]

Record Macro is not Possible....

 

[smartguy]

Please Helppppppp

 

[smartguy]

 

[JackDanIce]

Are all the names unique? If so, have you tried to use VLOOKUP? And why can't you record macro's?

 

[smartguy]

Hello sir,

I looking for VBA code...

I am not sure about record macro...

 

[MickG]

Hi, Smaryguy. Try this:-

[COLOR="Navy"]Sub[/COLOR] MG10Mar01
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, nDn [COLOR="Navy"]As[/COLOR] Range
With Sheets("Sheet2") '[COLOR="Green"][B]sht2[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] nRng = .Range(.Range("A2"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
With Sheets("Sheet1") '[COLOR="Green"][B]sht1[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A6"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] nDn [COLOR="Navy"]In[/COLOR] nRng
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] nDn = Dn And nDn(, 5) = Dn(, 2) [COLOR="Navy"]Then[/COLOR]
            Dn(, 3) = nDn(, 2)
            Dn(, 4) = nDn(, 3)
            Dn(, 5) = nDn(, 4)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] nDn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[smartguy]

Hi Mickg !!!

Great it's Working Fine...