Select Case Statement in For Each Loop

clueless1

New Member
Joined
Apr 16, 2013
Messages
17
Hi there, will do my best to be brief:

I have the code below to loop through cells in column A and then place a result into column B based upon the value in column A. I also want the value of the cell to be put to Upper Case only for the purposes of comparison, i.e. I do not need it changed in the actual data.

Code:
Sub Animals()
Dim rng As Range
Dim cell As Range
Dim result As String
Dim animal As String
Set rng = Range("A2:A30")

    For Each cell In rng
    
        animal = UCase(cell.Value)
    
        Select Case animal
        
        Case animal = "CAT"
        result = "have whiskers"
        
        Case animal = "DOG"
        result = "can catch a ball"
        
        
        Case animal = "APE"
        result = "are a dangerous animal!"
        
        Case Else
        result = "Animal Not Recognised"
        
        End Select
                
        cell.Offset(0, 1).Value = result
    
    Next cell
End Sub
The variable 'animal' does not seem to be getting the value in the cell, as all results are the 'Case Else' statement i.e. "Animal Not Recognised".

Please can anyone advise, must be something minor?!?!
 

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
Use Case "CAT" instead of Case animal = "CAT". The Select Case animal line signifies that you are selecting based on the animal variable.
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,039
or you can try below macro
Code:
Sub Animals()
Dim rng As Range
Set rng = Range("A2:A30")
For Each cell In rng
If UCase(cell) = "DOG" Then
cell.Offset(0, 1).Value = "can catch a ball"
ElseIf UCase(cell) = "CAT" Then
cell.Offset(0, 1).Value = "have whiskers"
ElseIf UCase(cell) = "APE" Then
cell.Offset(0, 1).Value = "are a dangerous animal!"
Else
cell.Offset(0, 1).Value = "Animal Not Recognised"
End If
Next
End Sub
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,039
ok then try below one
Code:
Sub Test()
For Each cell In Range("A2:A30")
Select Case UCase(cell)
Case "CAT"
cell.Offset(0, 1).Value = "have whiskers"
Case "DOG"
cell.Offset(0, 1).Value = "can catch a ball"
Case "APE"
cell.Offset(0, 1).Value = "are a dangerous animal!"
Case Else
cell.Offset(0, 1).Value = "Animal Not Recognised"
End Select
Next
End Sub
 

Forum statistics

Threads
1,082,151
Messages
5,363,441
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top