VBA Macro - Compile Error

[Isbenji]

Hello, I am contacting you because I am trying to create my first VBA macro. But when I am trying to build it, I receive the following error. I am sure I am totally wrong when it comes to the code as it is the first one I am building. If any excel expert could help me to understand what is wrong in the code.

VBA Code:
Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim r As Long

'Find last row in column AC with data
lastRow = Cells(Rows.Count, "AC").End(xlUp).Row

'Loop through all rows in column AC, starting with row 2
For r = 2 To lastRow
'   Check to see if value is EU Country
    If Cells(r, "AC") = "Germany" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Italy" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "France" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Spain" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Portugal" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Belgium" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Netherlands" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Austria" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Croatia" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Czech Republic" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Denmark" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Estonia" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Finland" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Greece" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Hungary" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Ireland" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Latvia" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Lithuania" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Luxembourg" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Malta" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Poland" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Romania" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Slovakia" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Slovenia" Then
    Cells(r, "AD") = "EU Country"
    If Cells(r, "AC") = "Sweden" Then
    Cells(r, "AD") = "EU Country"
    'ending to see if value is EU Country
    
    Else
        Cells(r, "AD") = "Non-EU Country"

        End If
    Next r
End Sub

 

[RoryA]

You have about 20 If clauses and only one closing End If statement. It looks like you should be using ElseIf lines, or a Select Case statement:

Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim r As Long

'Find last row in column AC with data
lastRow = Cells(Rows.Count, "AC").End(xlUp).Row

'Loop through all rows in column AC, starting with row 2
For r = 2 To lastRow
' Check to see if value is EU Country
Select Case Cells(r, "AC").Value
   Case "Germany", "Italy", "France", "Spain", "Portugal", "Belgium", "Netherlands", "Austria", "Croatia", "Czech Republic", "Denmark", "Estonia", "Finland", "Greece", "Hungary",  "Ireland", "Latvia", "Lithuania", "Luxembourg",  "Malta", "Poland", "Romania", "Slovakia", "Slovenia", "Sweden"
   Cells(r, "AD") = "EU Country"
'ending to see if value is EU Country

Case Else
   Cells(r, "AD") = "Non-EU Country"
End Select
Next r
End Sub

 

[Isbenji]

Thanks a lot for cleaning the code ! I was trying to polish it but couldnt wrap my head around it. Should there be something between Value and Case because the Case "Germany"... is appearing in red

 

[RoryA]

Sorry, I missed quotation mark in front of Sweden at the end of that line.

 

[Isbenji]

Thank you very much for your help