vba code for vlook loop

[Johnsmith999]

Hi,

I want to set up a simple vlu loop to copy data from one sheet to another

and I want the loop to end when when the loop reaches the end of the list.

let's assume the list of items with prices (columns 1&2) is on sheet 1 and the vlu required (column 3) is on sheet 2

Its early days for me, I am trying to teach myself using utube videos etc. but can't seem to grasp the idea of variables and how they interact with do until, do while, next, etc.

Please help..


[TABLE="width: 323"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]cake[/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[TD]tea[/TD]
[/TR]
[TR]
[TD]chips[/TD]
[TD="align: right"]1.20[/TD]
[TD][/TD]
[TD][/TD]
[TD]sandwich[/TD]
[/TR]
[TR]
[TD]chocolate[/TD]
[TD="align: right"]0.55[/TD]
[TD][/TD]
[TD][/TD]
[TD]pizza[/TD]
[/TR]
[TR]
[TD]crisp[/TD]
[TD="align: right"]0.70[/TD]
[TD][/TD]
[TD][/TD]
[TD]pastie[/TD]
[/TR]
[TR]
[TD]curry[/TD]
[TD="align: right"]2.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]drink[/TD]
[/TR]
[TR]
[TD]drink[/TD]
[TD="align: right"]0.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]curry[/TD]
[/TR]
[TR]
[TD]pastie[/TD]
[TD="align: right"]1.30[/TD]
[TD][/TD]
[TD][/TD]
[TD]crisp[/TD]
[/TR]
[TR]
[TD]pizza[/TD]
[TD="align: right"]1.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]chocolate[/TD]
[/TR]
[TR]
[TD]sandwich[/TD]
[TD="align: right"]1.99[/TD]
[TD][/TD]
[TD][/TD]
[TD]chips[/TD]
[/TR]
[TR]
[TD]tea[/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[TD]cake[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi and welcome to MrExcel.
This assumes that you have headers in row 1, with data starting in row2.

Sub JohnSmith999()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For i = 2 To UBound(Ary)
         .Item(Ary(i, 1)) = Ary(i, 2)
      Next i
      Ary = Sheets("sheet1").Range("A1").CurrentRegion.Value2
      For i = 2 To UBound(Ary)
         Ary(i, 3) = .Item(Ary(i, 1))
      Next i
   End With
   Sheets("sheet1").Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

 

[Johnsmith999]

Hi,

This doesn't appear to be related to what I am asking..

 

[Fluff]

With data on sheet1 like

Excel 2013/2016
	A	B
1	Pcode	Price
2	AL1 5EG	147
3	AL10 9WX	13
4	AL2 1UX	6
5	AL2 2EJ	19
6	AL2 2EX	12
7	AL2 3XZ	15
8	AL2 3YT	23
9	AL3 6AD	22
10	AL3 6NZ	5
Sheet1

And on sheet 2 like

Excel 2013/2016
	A	B
1	Pcode	Ward
2	AL1 5EG	Hatfield South West
3	AL10 9WX	London Colney
4	AL2 1UX	Aldenham West
5	AL2 2EJ	Sopwell
6	AL2 2EX	Park Street
7	AL2 3XZ	St Stephen
8	AL2 3YT	Verulam
9	AL3 6AD	Batchwood
10	AL3 6NZ	Cunningham
Sheet2

Do you want

Excel 2013/2016
	A	B	C
1	Pcode	Price	Ward
2	AL1 5EG	147	Hatfield South West
3	AL10 9WX	13	London Colney
4	AL2 1UX	6	Aldenham West
5	AL2 2EJ	19	Sopwell
6	AL2 2EX	12	Park Street
7	AL2 3XZ	15	St Stephen
8	AL2 3YT	23	Verulam
9	AL3 6AD	22	Batchwood
10	AL3 6NZ	5	Cunningham
Sheet1