Lookup Value and Replace Based on Table data

[MHamid]

Hello,

I need a formula that will replace the countries in a field in Sheet1 by looking at Sheet2 and replacing the country with the corresponding Country Code.
Below is just a sample of the data.

Sheet1:

Country

UK

Singapore

US

All Costa Rica;Argentina;Bahamas;Brazil;Colombia;Costa Rica CSC;Mexico;Venezuela

Ireland;UK

Jersey;Luxembourg;Switzerland;UK

Canada;US

Algeria;All Costa Rica;Argentina;Australia;Brazil;Bulgaria;Cameroon;Canada;Colombia;Congo;Costa Rica;Czech;Dominican Republic;Ecuador;Egypt;Gabon;Guatemala;Honduras;Hong Kong;Hungary;India;Indonesia;Israel;Kazakhstan;Kenya;Mexico;Nigeria;Pakistan;Panama;Paraguay;Peru;Philippines;Puerto Rico;Qatar;Romania;Russia;Singapore;Slovakia;South Africa;Switzerland;Taiwan;Tanzania;Thailand;Trinidad & Tobago;Turkey;UAE;UK;Ukraine;Uruguay;US;Venezuela;Zambia

Japan

Sheet2:

Country	Country Coded
Algeria	Algeria(DZ)
Argentina	Argentina(AR)
Aruba	Aruba(AW)
Asia Hub	Asia Hub(X6)
Australia	Australia(AU)
Austria	Austria(AT)

Thank you,
Miriam

 

[maabadi]

Are you have all country Names in one cell and want two replace them with hardcoded One b One?

Please Upload Example file & Desired Resultswith XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.

 

[maabadi]

I Suppose your Data at sheet1 is in Column A & At Sheet2 At Column A & B. This Macro Paste Result At Column B In Sheet1.
if you want replace Values. Change Cell.Offset(0, 1).Value = Val to Cell.Value = Val

Sub ReplaceHardCeded()
Dim Lr1 As Long, Lr2 As Long, Val As String, Cell As Range, I As Long
Dim Sh1 As Worksheet, Sh2 As Worksheet, Arr As Variant, NStr As String

On Error Resume Next
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
Lr2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Cell In Sh1.Range("A2:A" & Lr1)
 Arr = Split(Cell.Value, ";")

For I = 0 To UBound(Arr)
NStr = ""
NStr = Application.WorksheetFunction.VLookup(Arr(I), Sh2.Range("A2:B" & Lr2), 2, False)
If NStr = "" Then
NStr = Arr(I)
Else
Arr(I) = NStr
End If
If I > 0 Then
Val = Val & "; " & NStr
Else
Val = NStr
End If
Next I
Cell.Offset(0, 1).Value = Val
Val = ""
Next Cell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

 

[MHamid]

Hello,

I know it's been a while since I've looked at this post, but is there a way to update this code to be a UDF instead?


Thank you

 

[maabadi]

Try This:

Public Function ReplaceHardCoded(SrRange As Range) As String
Dim Lr1 As Long, Lr2 As Long, Val As String, Cell As Range, I As Long
Dim Sh1 As Worksheet, Sh2 As Worksheet, Arr As Variant, NStr As String

On Error Resume Next
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
Lr2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Arr = Split(SrRange.Value, ";")

For I = 0 To UBound(Arr)
NStr = ""
NStr = Application.WorksheetFunction.VLookup(Arr(I), Sh2.Range("A2:B" & Lr2), 2, False)
If NStr = "" Then
NStr = Arr(I)
Else
Arr(I) = NStr
End If
If I > 0 Then
Val = Val & "; " & NStr
Else
Val = NStr
End If
Next I
ReplaceHardCoded = Val
Val = ""
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Function

 

[MHamid]

Try This:

Public Function ReplaceHardCoded(SrRange As Range) As String
Dim Lr1 As Long, Lr2 As Long, Val As String, Cell As Range, I As Long
Dim Sh1 As Worksheet, Sh2 As Worksheet, Arr As Variant, NStr As String

On Error Resume Next
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
Lr2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Arr = Split(SrRange.Value, ";")

For I = 0 To UBound(Arr)
NStr = ""
NStr = Application.WorksheetFunction.VLookup(Arr(I), Sh2.Range("A2:B" & Lr2), 2, False)
If NStr = "" Then
NStr = Arr(I)
Else
Arr(I) = NStr
End If
If I > 0 Then
Val = Val & "; " & NStr
Else
Val = NStr
End If
Next I
ReplaceHardCoded = Val
Val = ""
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Function

How would this formula work? Would it just be

=ReplaceHardCoded(A2)

 

[maabadi]

Yes. only give it Source Cell Address.

 

[MHamid]

Yes. only give it Source Cell Address.

I’m not getting any results. My source cell is L6. So that’s what I did, but got blanks.

 

[maabadi]

Are you Changed your Table for Indexing Country names & replaces Phrases at Sheet 2. Or Is it moving to another place?

 

[MHamid]

HA! I see my mistake. I changed the function name and forgot to change it here

ReplaceHardCoded = Val

It works now. Thank you so much for your help.