If/Then Macro

[Ken McDaniel]

How do I write an If/Then/Else macro to work with a range
of values. For example, if a number is between 0 and 5% do
one thing but if it is between 0 and minus 5% do something
else. I have 10 ranges between -20% and +20%. Thanks.

 

[Ron de Bruin]

Check out the Select Case Statement in the VBA help Ken

 

[Tom Ogilvy]

use a case statment

Sub AAAA()
Dim cell As Range
For Each cell In Range("A1:A3")

Select Case cell.Value
Case -0.2 To -0.15
MsgBox "-0.2 to -0.15"

Case -0.15 To -0.1
MsgBox "-0.15 To -0.1"

Case -0.1 To -0.05
MsgBox "-0.1 To -0.05"

Case -0.05 To 0#
MsgBox "0.05 To 0.0"

Case 0# To 0.05
MsgBox "0.0 To 0.05"

Case 0.05 To 0.1
MsgBox "0.05 To 0.1"

Case 0.1 To 0.15
MsgBox "0.1 To 0.15"

Case 0.15 To 0.2
MsgBox "-0.2 to -0.15"

Case Else
MsgBox "out of bounds"
End Select
Next

End Sub


You could also calculate what interval it is in, but the usefulness of that
would depend on what kinds of things you are going to do.

 

[Don Guillett]

try this idea
Sub ifnum()
If [i1] > 20 Then [k2] = 20
If [i1] > 15 Then [k2] = 15
'etc
End Sub