If/Then Macro

  • Thread starter Thread starter Ken McDaniel
  • Start date Start date
K

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.
 
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.
 
try this idea
Sub ifnum()
If [i1] > 20 Then [k2] = 20
If [i1] > 15 Then [k2] = 15
'etc
End Sub
 
Back
Top