Code To Look For Number On Sheet 1 And Put Another Number Directly Below

[Dazzawm]

I have sheet 2 with a list of numbers in column A. I need a code please that will look for these numbers on sheet 1 in column AD and put the number that is next to it in column B on sheet 2 in the cell directly below in column Ad on sheet 1. Thanks.

Excel 2010
	A	B
1
2	Test	Test1
3	Data	Data1
Sheet2

Sheet 1 Before

Excel 2010
	AD
2	Test
3	NYA
4	NYA
5	Test
6	NLA
7	Data
8	NYA
9	NYA
10	NYA
11	NYA
12	Data
Sheet1

Sheet 1 After. I have highlighted the cells where the data has been added.

Excel 2010
	AD
2	Test
3	Test1
4	NYA
5	Test
6	Test1
7	Data
8	Data1
9	NYA
10	NYA
11	NYA
12	Data
13	Data1
Sheet1

Thanks.

 

[Dazzawm]

Any ideas anyone please?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Oct48
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("AD1"), .Range("AD" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
    .Item(Dn.Value) = Dn.Offset(, 1).Value
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng1
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] Dn.Offset(1) = .Item(Dn.Value)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Thanks Mick, but when I run it everything in column AD on sheet 1 disappears?

 

[MickG]

Have you got the sheet references correct ???
See example:-
https://app.box.com/s/09jphpfehvmy4k6ujg3d9va1xyu96uud

 

[Dazzawm]

Thanks got it working now. The file I was trying it on didn't have a header in row 1, so I put one there and it worked.

 

[Dazzawm]

Hi Mick. Could you tweak this a little further please. When the number is added below in AD, could a description that will be in column C on sheet 2 be added next to the added number in column AE on sheet 1 please. Thanks.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG13Oct00
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("AD1"), .Range("AD" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
   [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng1
    [COLOR="Navy"]If[/COLOR] .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(1).Value = .Item(Dn.Value).Offset(, 1).Value
        Dn.Offset(1, 1).Value = .Item(Dn.Value).Offset(, 2).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Thanks Mick.

 

[Dazzawm]

Hi Mick, I need a code something along these lines. Firstly the data in column C on sheet 1 needs to match. On sheet 2 there will be numbers in column A and another next to it in column B and a description in column C. When the number is found in column AD on sheet 1 then the number needs to be changed ABOVE this time and only if there is either 'NYA', 'N/A' or 'NLA' there'

On the example below the data in C matches and there is NYA in column AD so the MCA019 on sheet 2 can go above along with the description in C. But on the bottom row it is just a singular row so that needs to be ignored. It is only if there are multiple rows with matching data in C.

Sheet 1 before code

Excel 2010
	C	AD	AE
585	ASDB759 4004	NYA	Calcium Battery
586	ASDB759 4004	MIB019	Intel 4YR 95AH / 800SAE
587	ASDB759 4008	NYA	Calcium Battery
588	ASDB759 4008	MIB019	Intel 4YR 95AH / 800SAE
589	ASDB759 4011	MIB019	Intel 4YR 95AH / 800SAE
Sheet1

Sheet 1 after code

Excel 2010
	C	AD	AE
585	ASDB759 4004	MCA019	Calcium 3YR 100AH / 760EN
586	ASDB759 4004	MIB019	Intel 4YR 95AH / 800SAE
587	ASDB759 4008	MCA019	Calcium 3YR 100AH / 760EN
588	ASDB759 4008	MIB019	Intel 4YR 95AH / 800SAE
589	ASDB759 4011	MIB019	Intel 4YR 95AH / 800SAE
Sheet1

sheet 2

Excel 2010
	A	B	C
2	MIB019	MCA019	Calcium 3YR 100AH / 760EN
Sheet2

Thanks Mick