Using VLookup to overwrite existing value used to do the lookup

[TheRedCardinal]

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:

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:

[COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] aCell [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"D2:D" & TableRows[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]
            Lookupresult[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]WorksheetFunction[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]VLookup[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]aCell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ws2[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A:B"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][FONT=inherit][COLOR=#7d2727]2[/COLOR][/FONT][COLOR=#303336][FONT=inherit],0[/FONT][/COLOR][COLOR=#303336][FONT=inherit])
[/FONT][/COLOR][COLOR=#574123].Cells(CountryCell.Row, CountryNumber).Value = LookupResult[/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]      
[/FONT][/COLOR]</code>Next[COLOR=#303336][FONT=inherit] aCell[/FONT][/COLOR]

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:

[COLOR=#101094]For[/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] aCell [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7d2727]2, CountryColumn : Tablerows, CountryColumn[/COLOR][COLOR=#303336])[/COLOR]

But this didn't work.

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

 

[Fluff]

How about

For Each aCell In .Range(.Cells(2, CountryColumn), .Cells(Tablerows, CountryColumn))

 

[TheRedCardinal]

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

 

[Peter_SSs]

Not sure how many rows of data you might have but another option may be to process all the rows together

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