Results 1 to 4 of 4

Thread: vba code for vlook loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba code for vlook loop

    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..


    cake 0.75 tea
    chips 1.20 sandwich
    chocolate 0.55 pizza
    crisp 0.70 pastie
    curry 2.50 drink
    drink 0.50 curry
    pastie 1.30 crisp
    pizza 1.50 chocolate
    sandwich 1.99 chips
    tea 0.75 cake

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,166
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: vba code for vlook loop

    Hi and welcome to MrExcel.
    This assumes that you have headers in row 1, with data starting in row2.
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba code for vlook loop

    Hi,

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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,166
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: vba code for vlook loop

    With data on sheet1 like
    Excel 2013/2016
    AB
    1PcodePrice
    2AL1 5EG147
    3AL10 9WX13
    4AL2 1UX6
    5AL2 2EJ19
    6AL2 2EX12
    7AL2 3XZ15
    8AL2 3YT23
    9AL3 6AD22
    10AL3 6NZ5

    Sheet1




    And on sheet 2 like
    Excel 2013/2016
    AB
    1PcodeWard
    2AL1 5EGHatfield South West
    3AL10 9WXLondon Colney
    4AL2 1UXAldenham West
    5AL2 2EJSopwell
    6AL2 2EXPark Street
    7AL2 3XZSt Stephen
    8AL2 3YTVerulam
    9AL3 6ADBatchwood
    10AL3 6NZCunningham

    Sheet2





    Do you want
    Excel 2013/2016
    ABC
    1PcodePriceWard
    2AL1 5EG147Hatfield South West
    3AL10 9WX13London Colney
    4AL2 1UX6Aldenham West
    5AL2 2EJ19Sopwell
    6AL2 2EX12Park Street
    7AL2 3XZ15St Stephen
    8AL2 3YT23Verulam
    9AL3 6AD22Batchwood
    10AL3 6NZ5Cunningham

    Sheet1



    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •