Converting multiple if then statements to select case

[LuluZulu]

Hi, I am hoping someone can help here, I performed a search but did not see a result specific to my issue. We are pulling data from a database with several columns. Column A has country and column B has a subsidiary. Unfortunately, due to a bug in the system the countries don't appear, so in the interim I have created 3 if then statement as follows:

=IF(B2="company a","country 1",IF(B2="company b","country 2",IF(B2="company c","country 3",IF(B2="company d","country 4", etc

I would like to convert all 3 statements into a select case to make this process a little easier, but sadly my attempts have failed. I would be very grateful if someone can help.

 

[LuluZulu]

Testing now....

 

[paddydive]

hey..

may be something like this will work for you....

I have considered as you have selected the range where you want the Country name and the company name is in next column

like You have selected Range A1:A5 and company name is in B1:B5

use below code in addition to previous function

Sub enterFormula()

Dim rng As Range, rn As Range


Set rng = Selection

For Each rn In rng
rn.Formula = "=CountryNm(" & rn.Offset(0, 1).Address & ")"
Next

End Sub

 

[LuluZulu]

just change 100 to 100000 as many as u like.. enjoy..

That worked sky!!! Thank you so much, I really appreciate it!!

Paddy, there is no set range for the company name, it changes on a weekly basis. However, I can use your earlier UDF for smaller files and I can use sky's for the big beast. You guys are wonderful, thanks again!!!