Select Case Statement in For Each Loop

[clueless1]

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.

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]

Use Case "CAT" instead of Case animal = "CAT". The Select Case animal line signifies that you are selecting based on the animal variable.

 

[kevatarvind]

or you can try below macro

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

 

[clueless1]

Thank you, but I must use a Select Case statement.

 

[kevatarvind]

ok then try below one

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

 

[clueless1]

THANK YOU! - Sorry for he late reply!

 

[kevatarvind]

Your Welcome