Results 1 to 4 of 4

Thread: Using VLookup to overwrite existing value used to do the lookup

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Using VLookup to overwrite existing value used to do the lookup

    Hi all,

    My data is like this:

    • In one column (normally D, but could move) I have a 3 digit country code
    • Stored on another sheet, in A:B, I have a digit country code (A) and a 2 digit corresponding code (B)


    I want part of my macro to lookup the 3 digit code and replace it with the 2 in the same cell.

    I came up with this based on my reading around:

    Code:
    With ws1
            TableRows = .Cells(Rows.Count, 1).End(xlUp).Row
            Set CountryColumn = .Range("A1:Z1").Find("Country")
            CountryNumber = CountryColumn.Column
    
    
            For Counter = 2 To TableRows
                
                 LookupResult = Application.WorksheetFunction.VLookup(Cells(Counter, CountryColumn).Value, ws2.Range("A:B"), 2, 0)
                .Cells(CountryCell.Row, CountryNumber).Value = LookupResult
                
            Next Counter
        
        End With
    (You can assume all of the variables are set properly elsewhere)

    I had this working fine when the code was:

    Code:
    ForEach aCell In.Range("D2:D" & TableRows)
                Lookupresult= Application.WorksheetFunction.VLookup(aCell.Value, ws2.Range("A:B"),2,0)
    .Cells(CountryCell.Row, CountryNumber).Value = LookupResult
          
    Next aCell
    But this relied on the source data being in column D which can't always be relied upon.

    I then tried to turn that around to be:

    Code:
    ForEach aCell In.Range(2, CountryColumn : Tablerows, CountryColumn)
    But this didn't work.

    Also any suggestions on a better method e.g. using Match would be welcomed!

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

    Default Re: Using VLookup to overwrite existing value used to do the lookup

    How about
    Code:
    For Each aCell In .Range(.Cells(2, CountryColumn), .Cells(Tablerows, CountryColumn))
    - 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
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using VLookup to overwrite existing value used to do the lookup

    Worked perfectly thanks!
    Looks like I need to brush up on my cell and referencing knowledge!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Using VLookup to overwrite existing value used to do the lookup

    Not sure how many rows of data you might have but another option may be to process all the rows together
    Code:
    With ws1
      TableRows = .Cells(Rows.Count, 1).End(xlUp).Row
      Set CountryColumn = .Range("A1:Z1").Find("Country")
      CountryNumber = CountryColumn.Column
      With .Range(.Cells(2, "AZ"), .Cells(TableRows, "AZ"))
        .FormulaR1C1 = "=VLOOKUP(RC" & CountryNumber & ",'" & ws2.Name & "'!C1:C2,2,0)"
        .Copy
        .Parent.Cells(2, CountryNumber).PasteSpecial xlPasteValues
        .ClearContents
      End With
    End With
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •