Custom Function to read and write

**ditzer252** · 05-20-2009, 03:52 PM

Hello,

I'm very new to VBA and need some help.

I'm trying to create a custom function that will read a value in one cell and then write a different value in an adjacent cell.

More specifically, I have a list of 50 countries in one column, and I want to write a corresponding global region in the cell next to it. So if the if cell A1 is "USA" then the function would write "North America" in cell B1. Cell A2 might be "China" and the function would write "Asia" in cell B2.

I think I need to use If and Elseif, but I don't know how to put it together. How can I do this? Thanks for the help.

**stanleydgromjr** · 05-20-2009, 04:10 PM

ditzer252,

Welcome to the Excel Forum.

If you have more than 7 countries, you will not be able to use "If".

Please post your workbook - scroll down and see "Manage Attachments".

**ditzer252** · 05-20-2009, 05:01 PM

I won't post my workbook because it contains confidential data, sorry. Still, you can visualize a column with 50 unique countries running down. How can I place a regional label for each country in the next column? You said that I can't use if more than 7 times. I thought a custom function or user defined function was a way to working around the limitation of the nesting limit. Is there a different approach that I should take?

**stanleydgromjr** · 05-20-2009, 05:17 PM

ditzer252,

you can visualize a column with 50 unique countries running down. How can I place a regional label for each country in the next column?

I need to see the list of the 50 unique coutries and their respective regional labels.

You could use the VLOOKUP function with a list of 50 unique coutries and their respective regional labels.

Please post a sample of your workbook (without confidential information) - scroll down and see "Manage Attachments".

**ditzer252** · 05-21-2009, 08:33 AM

Stan,

Thanks for your help! I'm attaching a sample of my workbook, very basic. I want to be able to populate column B with the following 9 regions:

North America
South America
Central America
Caribbean
Europe
Africa
Asia
Oceania
Middle East

What do you think? How can I use VLOOKUP to read what is in column A and then write the correct region in column B?

**stanleydgromjr** · 05-21-2009, 08:54 AM

ditzer252,

Solved with using VLOOKUP.

See the attached workbook "countrylistsample(1).xls".

I created in Sheet2 a unique list of Countires, by using Data, Filter, AdvancedFilter, Unique.

After you manually complete/enter the Regions in Sheet2, in Sheet1 copy the formula in cell B2, down in column B, to the end of the data in column A.