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.

 

[paddydive]

Hi LuluZulu,

Create a below function,

Function CountryNm(CompanyNm As String)

Select Case CompanyNm

Case "company a": CountryNm = "Country 1"
Case "company b": CountryNm = "Country 2"
Case "company c": CountryNm = "Country 3"
Case "company d": CountryNm = "Country 4"
Case "company e": CountryNm = "Country 5"
Case "company f": CountryNm = "Country 6"
End Select

End Function

 

[LuluZulu]

Hi LuluZulu,

Create a below function,

Hi Paddy, thank you so much for responding. Unfortunately the Function does not work when I try to apply it. None of the cells in column A updates. I am hoping for a macro that will work automatically though, as there could be up to 1,000 entries sometimes.

 

[sky1in5]

Sub test()
Select Case Range("B2")
   Case "company a"
      Range("B2") = "Country 1"
   Case "company b"
      Range("B2") = "Country 2"
   Case "company c"
      Range("B2") = "Country 3"
   Case "company d"
      Range("B2") = "Country 4"
   End Select
End Sub

 

[paddydive]

Hi LuluZulu,

This is a UDF so you can use this just like you use other functions like sum, count etc

In A1 give formula like =CountryNm("B1")
and copy this formula to all other cells where you want the Country Name..

 

[LuluZulu]

Thanks sky1in5, I tried this and it updates the incorrect first row in the incorrect column (B2) with the relevant company name. But I am grateful that you posted it, because this is what I had tried before coming here, and so I think I am on the right track. When I use your version, only the first row updates. When I amend it so that the country range is A2, only the first correct row in Column A updates. So I amended the code as follows:

Sub test()
Select Case Range("B2:B1000")
Case "company a"
Range("A2") = "Country 1"
Case "company b"
Range("A2") = "Country 2"
Case "company c"
Range("A2") = "Country 3"
Case "company d"
Range("A2") = "Country 4"
End Select
End Sub

And I got an error "Type Mismatch". I am thinking perhaps it could work if we can select the ranges properly. Just to recap, I need to input the country name into Column A, based on the company name in Column B. Thanks again for all the quick responses.

 

[sky1in5]

Sub test()
for i = 1 to rows.count
Select Case Range("B:B")
Case "company a"
Range("A" & i) = "Country 1"
Case "company b"
Range("A" & i) = "Country 2"
Case "company c"
Range("A" & i) = "Country 3"
Case "company d"
Range("A" & i) = "Country 4"
End Select
next i
End Sub

try this..

 

[LuluZulu]

Hi LuluZulu,

This is a UDF so you can use this just like you use other functions like sum, count etc

In A1 give formula like =CountryNm("B1")
and copy this formula to all other cells where you want the Country Name..

Hi Paddy, thanks for the follow up. I see my error now. I saved it as a function and pulled it from the Insert Function button, but I used B1 instead of B2. It works, but I was still hoping for something more automated. In any case, thanks, this is much better than the several If statements that I am currently using. I will use this until I can design a macro that I can leave running while I get coffee.

 

[sky1in5]

Sub test()
For i = 1 To 100
Select Case Range("B" & i)
   Case "company a"
      Range("A" & i) = "Country 1"
   Case "company b"
      Range("A" & i) = "Country 2"
   Case "company c"
      Range("A" & i) = "Country 3"
   Case "company d"
      Range("A" & i) = "Country 4"
   End Select
Next i
End Sub

this should work..

 

[sky1in5]

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