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]

Can you help with this please Mick?

 

[Dazzawm]

Please let me know if you need any clarification or more examples.

 

[Dazzawm]

Hi Mick, could you let me know either way if you can help with this please, as I need it for work and will have to think of something else if you can't. Thanks.

 

[Dazzawm]

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

Hi Mick could you help with this code you did for me please. I need it amended slightly so that it only enters the data directly below if NLA, NYA or N/A are in the cell please. Please ignore everything from #10 to this post. Thanks.

 

[Dazzawm]

Also Mick which would be a great help, any cells that couldn't be changed because they had something other than NYA/NLA or NA, then maybe a summary with a list of these on sheet3?

 

[Dazzawm]

Can Mick or anyone else help with this please. Posts 1, 14 & 15 sum up what I need. Thanks.

 

[Dazzawm]

Please can someone help with this!

 

[Dazzawm]

I guess I'll have to think of something else on this one!

 

[Worf]

Do you still need help?
I’m going offline now and will return Monday evening…

 

[Dazzawm]

Yes I do please, thanks worf. Like I said before #1, #14 & #15 should explain enough what I need thanks.