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:

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

<colgroup><col><col></colgroup><tbody>
</tbody>

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.