PDA

View Full Version : Simple if statement



Justinbodden
09-17-2015, 07:10 AM
Hi All-

I am a novice VBA user so please bear with me.

I have a very simple IF statement that I want to execute in VBA. I have over 100 countries and I want a code that inserts in the empty column to the right of countries the corresponding region.

I.E- IF K10 = France Then L10 = Europe.
I.E. IF K11 = United States Then L11 = North America.

I need to do this for 138 countries.
How can I do this in VBA?
Any suggestions or faster alternatives?


I need to automate this task because we do have to do it for a monthly report and it takes to long to do a standard If statement.


Thanks in advance for all of your help. I am really excited to be apart of this community.

Regards,
Justin Bodden

Jan Karel Pieterse
09-17-2015, 07:17 AM
I would create a simple lookup table and use that as the source of a VLOOKUP function next to your data. No VBA requiered.
Suppose you have your countries and regions on a separate tab called C&R, then this formula gets the region belonging to a country (assuming countries are in col. And that their regions are in B):

=VLOOKUP(A2,'C&R'!$A$2:$B$200,2,FALSE)

dxider
09-17-2015, 07:24 AM
14408
Well, I think that what you need is not a VBA macro. The simple VLOOKUP formula can help you with this.

In another Sheet, create a table with all the values (Country | Region).
Then, on the K column of your Sheet, type the VLOOKUP formula =VLOOKUP(K10, tablewithvalues, 2,0)

This will search the value of your cell K10 in the list "tablewithvalues" and will return the second column as a result. The last parameter is to let excel know if the list is ordered or not.
Check the attached file.

Justinbodden
09-17-2015, 07:42 AM
Thanks so much for your help!

Justinbodden
09-17-2015, 10:05 AM
Hi Jan,

Is there a way that I can insert a "region" column to the right of the countries listed and have excel automatically perform the Vlookup (Vlookup information pulled from a separate sheet) with the push of a button.

Since I have to run these reports weekly, I am trying to find a way for excel to create the column and vlookup for me. I am tired of doing this every week.

I look forward to your response.

Thanks!