Case select error on string detection?

[RayCC]

just a simple Select case problem,
If I type the "text" or even any text in the Range("B3"), it will fall into "Case >200" statement, that means the answer of C3 is "Excellent"
If I place Case "text" before Case >200, it correctly display TEXT instead of "Excellent"
why any string or text will get into Case >200? they are not a number at all?

Sub Simple_Case()
    Select Case Range("B3").Value
   
        Case 1 To 200
            Range("C3").Value = "Good"
        Case 0
            Range("C3").Value = ""
        Case Is > 200 'cannot place before text otherwise all text belong in this statement
            Range("C3").Value = "Excellent"
        Case Is = "text"
            Range("C3").Value = "TEXT"
        Case Else
            Range("C3").Value = "Bad"
   
    End Select
End Sub

 

[RoryA]

It's the way comparisons are done in VBA. The number (200) is converted to text (so that the types match) and then compared to the entered text.

 

[nemmi69]

if its just a case of stopping text

Sub Simple_Case()
If IsNumeric(Range("B3").Value) Then
Select Case Range("B3").Value

Case 1 To 200
Range("C3").Value = "Good"
Case 0
Range("C3").Value = ""
Case Is > 200 'cannot place before text otherwise all text belong in this statement
Range("C3").Value = "Excellent"
Case Is = "text"
Range("C3").Value = "TEXT"
Case Else
Range("C3").Value = "Bad"

End Select

Else
    MsgBox "Entry is not a number"
End If
End Sub

 

[RayCC]

It's the way comparisons are done in VBA. The number (200) is converted to text (so that the types match) and then compared to the entered text.

got it. thanks a lot. which means I have to make sure the input can only be either all text variables or all numbers variables.

 

[RayCC]

if its just a case of stopping text

Sub Simple_Case()
If IsNumeric(Range("B3").Value) Then
Select Case Range("B3").Value

Case 1 To 200
Range("C3").Value = "Good"
Case 0
Range("C3").Value = ""
Case Is > 200 'cannot place before text otherwise all text belong in this statement
Range("C3").Value = "Excellent"
Case Is = "text"
Range("C3").Value = "TEXT"
Case Else
Range("C3").Value = "Bad"

End Select

Else
    MsgBox "Entry is not a number"
End If
End Sub

great suggestion... thanks for your help!!!

 

[nemmi69]

No problem, could divided in to 2 cases. One for text one for numbers if required.

 

[nemmi69]

Cleaned version

Sub Simple_Case()
If IsNumeric(Range("B3").Value) Then
    
    Select Case Range("B3").Value
        Case 1 To 200
        Range("C3").Value = "Good"
        Case 0
        Range("C3").Value = ""
        Case Is > 200
        Range("C3").Value = "Excellent"
        Case Else
        Range("C3").Value = "Bad"
    End Select
Else
    MsgBox "Entry is not a number"
End If
End Sub

 

[jkpieterse]

You could simply check for the type in the cell:

    Select Case TypeName(Range("a1").Value)
    Case "String"
        MsgBox "text!"
    Case "Date"
    Case "Double"
        'Your Select case goes here
    End Select