Macro: If X then return Y on next sheet.

Lorr81

New Member
Joined
Mar 21, 2016
Messages
38
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top