TheRedCardinal
Active Member
- Joined
- Jul 11, 2019
- Messages
- 252
- Office Version
- 365
- 2021
- Platform
- Windows
Hi all,
My data is like this:
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:
(You can assume all of the variables are set properly elsewhere)
I had this working fine when the code was:
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:
But this didn't work.
Also any suggestions on a better method e.g. using Match would be welcomed!
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:
[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:
Code:
[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!