Using a IF statement.

[SKR]

I am new in the field of VBA and would like to advance my knowledge on this area. Upon doing this, I was wondering how to make a macro to check an active cells value and came up with the following:

Sub CheckActive()


If ActiveCell >= "1000" Then
ActiveCell.Offset(0, -2).Value = "Higher than / Equal to 1000"
ElseIf ActiveCell >= "100" Then
ActiveCell.Offset(0, -2).Value = "Higher than / Equal to 100"
ElseIf ActiveCell >= "10" Then
ActiveCell.Offset(0, -2).Value = "Higher than / Equal to 10"
Else
ActiveCell.Offset(0, -2).Value = "Lower than 10"
End If

End Sub

Problem is, that the first IF statement is found true everytime.

I assume the problem lies in the definition of the IF statement, but being new to this, I am unsure.

Any help, hints or pointers would be highly appreciated.


_________________

Best regards,
Steven Kristjansson

[ This Message was edited by: SKR on 2002-05-26 07:56 ]

[SKR]

Got the previous to work with setting it up like this instead:

Sub CheckActive()


If ActiveCell.Value >= 1000 Then
ActiveCell.Offset(0, -2).Value = "Higher than / Equal to 1000"
ElseIf ActiveCell.Value >= 100 Then
ActiveCell.Offset(0, -2).Value = "Higher than / Equal to 100"
ElseIf ActiveCell.Value >= 10 Then
ActiveCell.Offset(0, -2).Value = "Higher than / Equal to 10"
Else
ActiveCell.Offset(0, -2).Value = "Lower than 10"
End If

End Sub

[Tom Schreiner]

An alternative to multiple If statements is a more readable Select Case...

Sub CheckActive()

Dim MyValue As String

Select Case ActiveCell.Value

    Case Is < 10: MyValue = "Lower than 10"

    Case Is < 100: MyValue = "Higher than / Equal to 10"

    Case Is < 101: MyValue = "Higher than / Equal to 100"

    Case Else: MyValue = "Higher than / Equal to 1000"

End Select

ActiveCell.Offset(0, -2).Value = MyValue

End Sub

Tom