Excel VBA ElseIf query

Question

The following little piece of vba returns the required value in cell B2. I thought by changing the Range to ("A:A") and ("B:B") I could get it to run through an entire spreadsheet. However it fails to run when I try this. The full list has 15 countries so an IF function copied down column B won't work. Any help would be much appreciated. Thanks in advance.

    Sub test()

    Dim Country As String, ID As Integer
    Country = Range("A2").Value

    If Country = "France" Then
        ID = 1
    ElseIf Country = "Germany" Then
        ID = 2
    ElseIf Country = "Spain" Then
        ID = 3
    ElseIf Country = "Italy" Then
        ID = 4
    Else
        Code = 0
    End If

    Range("B2").Value = ID

    End sub

Answer 1

Commenter above is correct, you should look into using the select statement, but I'd be a liar if I said I didn't use a bunch of ElseIf statements when I'm feeling lazy... Anyway, I'm assuming you want this to check the value in column A and return the ID code for each cell in A in column B. What you need is a for each loop... try this:

Sub test()

Dim Country As String, ID As Integer

Dim Rng As Range
Dim Cel As Range

Set Rng = Range("A:A")

For Each Cel In Rng

    Country = Cel.Value

    If Country = "France" Then
        ID = 1
    ElseIf Country = "Germany" Then
        ID = 2
    ElseIf Country = "Spain" Then
        ID = 3
    ElseIf Country = "Italy" Then
        ID = 4
    End If

    Cel.Offset(0, 1).Value = ID

Next Cel

End Sub

So what you're doing is looping through each Cell (Cel) in the Range (Rng) of column A. When you return your ID, you're just offseting the Cell (Cel) from column A to the Cell in column B (Cel.Offset(0, 1)).

This will work, but it might be kinda slow because it will cycle through all ranges. What you could also do is find the last row somehow. There are more interesting ways to do it, but if I were you I'd just loop through using an integer counter:

Sub test()

Dim Country As String, ID As Integer

Dim intCounter As Integer

intCounter = 1

Do Until Cells(intCounter, 1).Value = ""

    Country = Cells(intCounter, 1).Value

    If Country = "France" Then
        ID = 1
    ElseIf Country = "Germany" Then
        ID = 2
    ElseIf Country = "Spain" Then
        ID = 3
    ElseIf Country = "Italy" Then
        ID = 4
    End If

    Cells(intCounter, 2).Value = ID

    intCounter = intCounter + 1

Loop

End Sub

The above would have the exact same results, but you wouldn't have to go through every cell in column A, only the cells that are populated.

Edit:

Since it was mentioned bellow, I thought I'd toss in an example of how to do this with a collection:

Sub test()

Dim intCounter As Integer

Dim countries As Collection
Set countries = New Collection

'Here is where you'd add all your country codes:
countries.Add 1, "France"
countries.Add 2, "Germany"
countries.Add 3, "Spain"
countries.Add 4, "Italy"
countries.Add 232, "Ireland"

'Now just loop through
intCounter = 1
Do Until Cells(intCounter, 1).Value = ""
    'Next line returns the numeric value (the item) from countries based
    'on the value contained in the cell to the left in column A and writes
    'it to column B.
    Cells(intCounter, 2).Value = countries(Cells(intCounter, 1).Value)
    intCounter = intCounter + 1
Loop

End Sub

That is actually how I'd perform this kind of task. I'd also consider placing On Error Resume Next before the Cells(intCounter, 2).value = countries... line, and On Error GoTo 0 after that line. This will skip any cells that had a country string not contained in the collection. The on error resume next statement simply tells the compiler to ignore the next error it finds. Since we are only looking for an error on that next line, we turn error handling off using the on error goto 0 line on the next line. It is handy when using collections. Based on these fundamentals you can effectively "test" a collection for an item using some creativity. Depending on how reliable I thoguht the input on the worksheet was, I might also use Trim() on the string returned from the cell in column A to remove any accidental extra spaces.

Answer 2

Something like this should be easier for you to maintain

Sub Test()

    Dim ID As Integer
    Dim Country As Object

    Set Country = CreateObject("Scripting.Dictionary")
    Country.Add "France", 1
    Country.Add "Germany", 2
    Country.Add "Spain", 3
    Country.Add "Italy", 4

    For Each Cell In Range("A2:A7")
        If Country.Exists(Cell.Value) Then
            Cell.Offset(0, 1).Value = Country(Cell.Value)
        Else
            Cell.Offset(0, 1).Value = 0
        End If
    Next

    Country.RemoveAll
    Set Country = Nothing

End Sub

Answer 3

You should probably use a For loop. Take a look at Select Case statement as well, it's better than multiple If.