Can I simplify these IF statements

[Guest]

I have a named range "NUMBER". When its value is 5, 10, 15, 20, 25, 30 or 35
I want to run sub CopyAggregate(). Is there a more elegant way to write this?

If Range("NUMBER").Value = 5 Then CopyAggregate
ElseIf Range("NUMBER").Value = 10 Then CopyAggregate
ElseIf Range("NUMBER").Value = 15 Then CopyAggregate
ElseIf Range("NUMBER").Value = 20 Then CopyAggregate
ElseIf Range("NUMBER").Value = 25 Then CopyAggregate
ElseIf Range("NUMBER").Value = 30 Then CopyAggregate
ElseIf Range("NUMBER").Value = 35 Then CopyAggregate
End If

Thanks for any suggestions.

 

[Guest]

Try something like this:

'--------Start of Code------------
Select Case Range("number").Value
Case Is = 5, 10, 15, 20, 25, 30
CopyAggregate
Case Else
MsgBox "Does not meet criteria"
End Select
'--------End of Code------------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Norman Yuan]

SELECT Case Range("NUMBER").Value
Case 5, 10, 15, 20, 25, 30, 35
CopyAggregate
Case Else
'Do something or do nothing
End Select

 

[Guest]

maybe like this

With Range("NUMBER")
If (.Value Mod 5 = 0) And (.Value > 0) And (.Value < 36) Then
CopyAggregate
End With

 

[Guest]

Norman - Thanks for the help. I have never seen Select Case before. I think
it will work just fine.

 

[Guest]

Ron - Thanks for the help. I have never seen Select Case before. I think it
will work just fine.

 

[Guest]

I'm glad I could help.....and thanks much for the feedback!


***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks Vergel. I've been looking in VB help trying to get my brain around the
Mod operator. I don't understand how it works yet. I will keep trying.