Macro: If X then return Y on next sheet.

[Lorr81]

Hi,

I have a two sheets and I need to copy data from sheet one and paste it in sheet 2, the issue is that each contact on sheet 1 has a field called country of birth which is fine however to use that information and paste it next to their record on sheet 2 but with a reference number instead of the word, not even sure if this is possible but would be grateful for any help.

Please see below:

[TABLE="width: 505"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet 1 - Country - Column I[/TD]
[TD]Sheet 2 - Ref - Column K[/TD]
[/TR]
[TR]
[TD]Libya[/TD]
[TD="align: right"]434[/TD]
[/TR]
[TR]
[TD]New Caledonia[/TD]
[TD="align: right"]540[/TD]
[/TR]
[TR]
[TD]St Vincent and the Grenadines[/TD]
[TD="align: right"]670[/TD]
[/TR]
[TR]
[TD]Tanzania Untd Republic of[/TD]
[TD="align: right"]834[/TD]
[/TR]
[TR]
[TD]Liechtenstein[/TD]
[TD="align: right"]438[/TD]
[/TR]
[TR]
[TD]New Zealand[/TD]
[TD="align: right"]554[/TD]
[/TR]
[TR]
[TD]Saint Pierre and Miquelon[/TD]
[TD="align: right"]666[/TD]
[/TR]
[TR]
[TD]Thailand[/TD]
[TD="align: right"]764[/TD]
[/TR]
[TR]
[TD]Lithuania[/TD]
[TD="align: right"]440[/TD]
[/TR]
[TR]
[TD]Nicaragua[/TD]
[TD="align: right"]558[/TD]
[/TR]
[TR]
[TD]Samoa[/TD]
[TD="align: right"]882[/TD]
[/TR]
[TR]
[TD]Timor-Leste[/TD]
[TD="align: right"]626[/TD]
[/TR]
[TR]
[TD]Luxembourg[/TD]
[TD="align: right"]442[/TD]
[/TR]
[TR]
[TD]Niger[/TD]
[TD="align: right"]562[/TD]
[/TR]
[TR]
[TD]San Marino[/TD]
[TD="align: right"]674[/TD]
[/TR]
[TR]
[TD]Togo[/TD]
[TD="align: right"]768[/TD]
[/TR]
[TR]
[TD]Macao[/TD]
[TD="align: right"]446[/TD]
[/TR]
[TR]
[TD]Nigeria[/TD]
[TD="align: right"]566[/TD]
[/TR]
[TR]
[TD]Sao Tome and Principe[/TD]
[TD="align: right"]678[/TD]
[/TR]
[TR]
[TD]Tokelau[/TD]
[TD="align: right"]772[/TD]
[/TR]
[TR]
[TD]Macedonia the former Yugoslav Republic of[/TD]
[TD="align: right"]807[/TD]
[/TR]
[TR]
[TD]Niue[/TD]
[TD="align: right"]570[/TD]
[/TR]
[TR]
[TD]Saudi Arabia[/TD]
[TD="align: right"]682[/TD]
[/TR]
[TR]
[TD]Tonga[/TD]
[TD="align: right"]776[/TD]
[/TR]
[TR]
[TD]Madagascar[/TD]
[TD="align: right"]450[/TD]
[/TR]
[TR]
[TD]Norfolk Island[/TD]
[TD="align: right"]574[/TD]
[/TR]
[TR]
[TD]Senegal[/TD]
[TD="align: right"]686[/TD]
[/TR]
[TR]
[TD]Trinidad and Tobago[/TD]
[TD="align: right"]780[/TD]
[/TR]
[TR]
[TD]Malawi[/TD]
[TD="align: right"]454[/TD]
[/TR]
[TR]
[TD]Northern Mariana Islands[/TD]
[TD="align: right"]580[/TD]
[/TR]
[TR]
[TD]Serbia[/TD]
[TD="align: right"]688[/TD]
[/TR]
[TR]
[TD]Tunisia[/TD]
[TD="align: right"]788[/TD]
[/TR]
[TR]
[TD]Malaysia[/TD]
[TD="align: right"]458[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD="align: right"]578[/TD]
[/TR]
[TR]
[TD]Seychelles[/TD]
[TD="align: right"]690[/TD]
[/TR]
[TR]
[TD]Turkey[/TD]
[TD="align: right"]792[/TD]
[/TR]
[TR]
[TD]Maldives[/TD]
[TD="align: right"]462[/TD]
[/TR]
[TR]
[TD]Oman[/TD]
[TD="align: right"]512[/TD]
[/TR]
[TR]
[TD]Sierra Leone[/TD]
[TD="align: right"]694[/TD]
[/TR]
[TR]
[TD]Turkmenistan[/TD]
[TD="align: right"]795[/TD]
[/TR]
[TR]
[TD]Mali[/TD]
[TD="align: right"]466[/TD]
[/TR]
[TR]
[TD]Pakistan[/TD]
[TD="align: right"]586[/TD]
[/TR]
[TR]
[TD]Singapore[/TD]
[TD="align: right"]702[/TD]
[/TR]
[TR]
[TD]Turks and Caicos Islands[/TD]
[TD="align: right"]796[/TD]
[/TR]
[TR]
[TD]Malta[/TD]
[TD="align: right"]470[/TD]
[/TR]
[TR]
[TD]Palau[/TD]
[TD="align: right"]585[/TD]
[/TR]
[TR]
[TD]Sint Martin (Dutch part)[/TD]
[TD="align: right"]534[/TD]
[/TR]
[TR]
[TD]Tuvalu[/TD]
[TD="align: right"]798[/TD]
[/TR]
[TR]
[TD]Marshall Islands[/TD]
[TD="align: right"]584[/TD]
[/TR]
[TR]
[TD]Palestine, State of[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD]Slovakia[/TD]
[TD="align: right"]703[/TD]
[/TR]
[TR]
[TD]Uganda[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD]Martinique[/TD]
[TD="align: right"]474[/TD]
[/TR]
[TR]
[TD]Panama[/TD]
[TD="align: right"]591[/TD]
[/TR]
[TR]
[TD]Slovenia[/TD]
[TD="align: right"]705[/TD]
[/TR]
[TR]
[TD]Ukraine[/TD]
[TD="align: right"]804[/TD]
[/TR]
[TR]
[TD]Mauritania[/TD]
[TD="align: right"]478[/TD]
[/TR]
[TR]
[TD]Papua New Guinea[/TD]
[TD="align: right"]598[/TD]
[/TR]
[TR]
[TD]Solomon Islands[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]United Arab Emirates[/TD]
[TD="align: right"]784[/TD]
[/TR]
[TR]
[TD]Mauritius[/TD]
[TD="align: right"]480[/TD]
[/TR]
[TR]
[TD]Paraguay[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD]Somalia[/TD]
[TD="align: right"]706[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD="align: right"]826[/TD]
[/TR]
[TR]
[TD]Mayotte[/TD]
[TD="align: right"]175[/TD]
[/TR]
[TR]
[TD]Peru[/TD]
[TD="align: right"]604[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD="align: right"]710[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD="align: right"]840[/TD]
[/TR]
[TR]
[TD]Mexico[/TD]
[TD="align: right"]484[/TD]
[/TR]
[TR]
[TD]Philippines[/TD]
[TD="align: right"]608[/TD]
[/TR]
[TR]
[TD]South Georgia and the South Sandwich Islands[/TD]
[TD="align: right"]239[/TD]
[/TR]
[TR]
[TD]US Minor Outlying Islands[/TD]
[TD="align: right"]581[/TD]
[/TR]
[TR]
[TD]Micronesia Federated States of[/TD]
[TD="align: right"]583[/TD]
[/TR]
[TR]
[TD]Pitcairn[/TD]
[TD="align: right"]612[/TD]
[/TR]
[TR]
[TD]South Sudan[/TD]
[TD="align: right"]728[/TD]
[/TR]
[TR]
[TD]Unknown[/TD]
[TD="align: right"]999[/TD]
[/TR]
[TR]
[TD]Moldova[/TD]
[TD="align: right"]498[/TD]
[/TR]
[TR]
[TD]Poland[/TD]
[TD="align: right"]616[/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD="align: right"]724[/TD]
[/TR]
[TR]
[TD]Uruguay[/TD]
[TD="align: right"]858[/TD]
[/TR]
[TR]
[TD]Monaco[/TD]
[TD="align: right"]492[/TD]
[/TR]
[TR]
[TD]Portugal[/TD]
[TD="align: right"]620[/TD]
[/TR]
[TR]
[TD]Sri Lanka[/TD]
[TD="align: right"]144[/TD]
[/TR]
[TR]
[TD]Uzbekistan[/TD]
[TD="align: right"]860[/TD]
[/TR]
[TR]
[TD]Mongolia[/TD]
[TD="align: right"]496[/TD]
[/TR]
[TR]
[TD]Puerto Rico[/TD]
[TD="align: right"]630[/TD]
[/TR]
[TR]
[TD]St Helena Ascension & Tristan da Cunha[/TD]
[TD="align: right"]654[/TD]
[/TR]
[TR]
[TD]Vanuatu[/TD]
[TD="align: right"]548[/TD]
[/TR]
[TR]
[TD]Montenegro[/TD]
[TD="align: right"]499[/TD]
[/TR]
[TR]
[TD]Qatar[/TD]
[TD="align: right"]634[/TD]
[/TR]
[TR]
[TD]Sudan[/TD]
[TD="align: right"]736[/TD]
[/TR]
[TR]
[TD]Venezuela[/TD]
[TD="align: right"]862[/TD]
[/TR]
[TR]
[TD]Montserrat[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Reunion[/TD]
[TD="align: right"]638[/TD]
[/TR]
[TR]
[TD]Suriname[/TD]
[TD="align: right"]740[/TD]
[/TR]
[TR]
[TD]Viet Nam[/TD]
[TD="align: right"]704[/TD]
[/TR]
[TR]
[TD]Morocco[/TD]
[TD="align: right"]504[/TD]
[/TR]
[TR]
[TD]Romania[/TD]
[TD="align: right"]642[/TD]
[/TR]
[TR]
[TD]Svalbard and Jan Mayen[/TD]
[TD="align: right"]744[/TD]
[/TR]
[TR]
[TD]Virgin Islands British[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD]Myanmar[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD]Russian Federation[/TD]
[TD="align: right"]643[/TD]
[/TR]
[TR]
[TD]Swaziland[/TD]
[TD="align: right"]748[/TD]
[/TR]
[TR]
[TD]Virgin Islands U.S.[/TD]
[TD="align: right"]850[/TD]
[/TR]
[TR]
[TD]Mozambique[/TD]
[TD="align: right"]508[/TD]
[/TR]
[TR]
[TD]Rwanda[/TD]
[TD="align: right"]646[/TD]
[/TR]
[TR]
[TD]Sweden[/TD]
[TD="align: right"]752[/TD]
[/TR]
[TR]
[TD]Wallis and Futuna[/TD]
[TD="align: right"]876[/TD]
[/TR]
[TR]
[TD]Namibia[/TD]
[TD="align: right"]516[/TD]
[/TR]
[TR]
[TD]Saint Barthelemy[/TD]
[TD="align: right"]652[/TD]
[/TR]
[TR]
[TD]Switzerland[/TD]
[TD="align: right"]756[/TD]
[/TR]
[TR]
[TD]Western Sahara[/TD]
[TD="align: right"]732[/TD]
[/TR]
[TR]
[TD]Nauru[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]Saint Kitts and Nevis[/TD]
[TD="align: right"]659[/TD]
[/TR]
[TR]
[TD]Syrian Arab Republic[/TD]
[TD="align: right"]760[/TD]
[/TR]
[TR]
[TD]Yemen[/TD]
[TD="align: right"]887[/TD]
[/TR]
[TR]
[TD]Nepal[/TD]
[TD="align: right"]524[/TD]
[/TR]
[TR]
[TD]Saint Lucia[/TD]
[TD="align: right"]662[/TD]
[/TR]
[TR]
[TD]Taiwan Province of China[/TD]
[TD="align: right"]158[/TD]
[/TR]
[TR]
[TD]Zambia[/TD]
[TD="align: right"]894[/TD]
[/TR]
[TR]
[TD]Netherlands[/TD]
[TD="align: right"]528[/TD]
[/TR]
[TR]
[TD]Saint Martin (French part)[/TD]
[TD="align: right"]663[/TD]
[/TR]
[TR]
[TD]Tajikistan[/TD]
[TD="align: right"]762[/TD]
[/TR]
[TR]
[TD]Zimbabwe[/TD]
[TD="align: right"]716[/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[My Aswer Is This]

Where on sheet 1 is the reference number.

 

[Lorr81]

Hi,
the reference number does not exist on sheet 1 only the country, If I could even find and replace I could then copy to sheet 2.

 

[Stiuart_W]

Sorry but your request is not clear enough.

Please clarify your requirements

 

[My Aswer Is This]

So on sheet1 column I you have country names

And on sheet2 column K you have reference numbers

So how would a script know which reference number matches up with Sweden for example?

 

[Lorr81]

Ok, so in sheet 1 I have column I which contains the countries,
In sheet 2 I have Column K which is Blank,
I need to search sheet 1 column I and if Libya is found the macro will populate Sheet 2 Column K with the Ref 434. If drafted the following macro to find and replace on sheet 1 which seems to work I just need to figure out how to set it to only change Sheet 1.

Sub Multi_FindReplace()




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long


fndList = Array("Libya", "New Caledonia", "St Vincent and the Grenadines", "Tanzania Untd Republic of", "Liechtenstein", "New Zealand", "Saint Pierre and Miquelon", "Thailand", "Lithuania")
rplcList = Array("434", "540", "670", "834", "438", "554", "666", "764", "440")


'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In "SF Export"
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht

Next x


End Sub


Thank you for your help.