VBA Excel IF

I

Isbenji

New Member
Joined
Jul 7, 2020
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am building my first VBA Macro Excel (button) and I need help with it as I am unfortunately unsuccessful. Basically in one column, I have name of the countries and I would like in the second column to have if it is a EU Country or Non-EU Country. I tried to test it only with one country (Germany) but I am still unsuccessful and it gives me the following error message

1594150690010.png


Dim customernationality As Integer, result As String
customernationality = Columns("AC").Value

If customernationality = Germany Then result = "EU Country"

Columns("AD").Value = result

I guess the code is wrong, if I may have some help with it :)

Thanks in advance
Benjamin
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Welcome to the Board!

A few problems right off the bat.

What are you trying to capture with your "customernationality" variable?
You have declared it to be an Integer, but then look like you are trying to set it equal to the whole column "AC" (which you cannot do), instead of a specific cell?
What exacltly are you trying to do with this?

Also, it is important to note that anything enclosed in double-quotes is treated as literal text, and anything without quotes is treated as a variable.
So this:
If customernationality = Germany Then result = "EU Country"
would need to look like this:
If customernationality = "Germany" Then result = "EU Country"
as you do not have any variable named Germany

If your intention is to go through each value in column AC and check it, then you will need a loop, i.e.

VBA Code: 
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 Germany
    If Cells(r,"AC") = "Germany" Then Cells(r,"AD") = "EU Country"
Next r
 
Upvote 0
Thanks a lot ! That was exactly what I was looking for. I needed to have in the column AD "EU Country" if the column AC has country belonging to the EU. For the rest of the world, should I just then add

Else
result = "Non-EU Country"
 
Upvote 0
Isbenji said:
Thanks a lot ! That was exactly what I was looking for. I needed to have in the column AD "EU Country" if the column AC has country belonging to the EU. For the rest of the world, should I just then add

Else
result = "Non-EU Country"
Click to expand...
You are welcome.

Note that you do not need to use a "result" variable at all. You can just do:
VBA Code: 
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 Germany
    If Cells(r,"AC") = "Germany" Then 
        Cells(r,"AD") = "EU Country"
    Else
        Cells(r,"AD") = "Non-EU Country" 
Next r
 
Upvote 0
Thank you so much once again ! I am starting and I am really unsure of the meaning of coding (Just trying by copy and past of examples, I find)

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"

Else
Cells(r, "AD") = "Non-EU Country"

End If
Next r
End Sub

I am trying to launch it but I received the error message "Compile Error: Else without If"
Should I add to the code Sub Else_Without_If()
 
Upvote 0
Isbenji said:
Thank you so much once again ! I am starting and I am really unsure of the meaning of coding (Just trying by copy and past of examples, I find)

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"

Else
Cells(r, "AD") = "Non-EU Country"

End If
Next r
End Sub

I am trying to launch it but I received the error message "Compile Error: Else without If"
Should I add to the code Sub Else_Without_If()
Click to expand...

I actually fix this but then I have the error message "Compile error: Next without For"

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
 
Upvote 0
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
 
Upvote 0
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

I am getting the following error when I try to use the button function. I would need a bright mind to help me with it. I am trying my first VBA and I am surely wrong on the whole coding part

1594167878125.png


Thanks in advance :)
 
Upvote 0
It needs to be like
VBA Code: 
For r = 2 To lastRow
'   Check to see if value is EU Country
    If Cells(r, "AC") = "Germany" Then
      Cells(r, "AD") = "EU Country"
    ElseIf Cells(r, "AC") = "Italy" Then
      Cells(r, "AD") = "EU Country"
    ElseIf Cells(r, "AC") = "France" Then
      Cells(r, "AD") = "EU Country"
    ElseIf Cells(r, "AC") = "Spain" 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
 
Upvote 0
You can shorten your code by placing all your countries into a single array, then check to see if the value in each row of column AC is found in that array, like this:
VBA Code: 
Private Sub CommandButton1_Click()

    Dim lastRow As Long
    Dim r As Long
    Dim countryArray
    Dim i As Long
    Dim fnd As Boolean
    
'   Set country array
    countryArray = Array("Germany", "Italy", "France", "Spain", "Portugal", "Belgium", "Netherlands", "Austria", "Croatia", "Czech Republic", "Denmark")
    
'   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
'       Set default value of "fnd" to False
        fnd = False
    '       Check to see if value is in array
        For i = LBound(countryArray) To UBound(countryArray)
            If countryArray(i) = Cells(r, "AC") Then
                fnd = True
                Exit For
            End If
        Next i
'       Populate column AD
        If fnd Then
            Cells(r, "AD") = "EU Country"
        Else
            Cells(r, "AD") = "Non-EU Country"
        End If
    Next r
    
End Sub
(Note that I did not put ALL of your listed countries in the array above - you can add the rest).
 
Upvote 0
You must log in or register to reply here.

Similar threads

drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom
G
  • Question Question
VBA: Split a table into multiple tables across different worksheets?
Replies
1
Views
436
dermie_72
D
P
  • Solved
MAXIFS/MINIFS
Replies
8
Views
584
Peter_SSs
Peter_SSs
Livin404
  • Question Question
MACRO clearing out formula in Column E.
Replies
7
Views
283
Livin404
Livin404
PrinceOfDarkness
  • Solved
Creating folder, subfolder and subsubfolder based on range
Replies
4
Views
535
PrinceOfDarkness
PrinceOfDarkness

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top