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.
--
Jim T

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


"Jim Tibbetts" wrote:

> 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.
> --
> Jim T

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

"Jim Tibbetts" <(E-Mail Removed)> wrote in message
news:2FBCE892-B1A9-475C-BBA4-(E-Mail Removed)...
>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.
> --
> Jim T

maybe like this

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


--
Hope that helps.

Vergel Adriano


"Jim Tibbetts" wrote:

> 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.
> --
> Jim T

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


"Norman Yuan" wrote:

> SELECT Case Range("NUMBER").Value
> Case 5, 10, 15, 20, 25, 30, 35
> CopyAggregate
> Case Else
> 'Do something or do nothing
> End Select
>
> "Jim Tibbetts" <(E-Mail Removed)> wrote in message
> news:2FBCE892-B1A9-475C-BBA4-(E-Mail Removed)...
> >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.
> > --
> > Jim T
>
>
>

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


"Ron Coderre" wrote:

> 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
>
>
> "Jim Tibbetts" wrote:
>
> > 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.
> > --
> > Jim T

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


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

XL2002, WinXP


"Jim Tibbetts" wrote:

> Ron - Thanks for the help. I have never seen Select Case before. I think it
> will work just fine.
> --
> Jim T
>
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "Jim Tibbetts" wrote:
> >
> > > 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.
> > > --
> > > Jim T

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.
--
Jim T


"Vergel Adriano" wrote:

> maybe like this
>
> With Range("NUMBER")
> If (.Value Mod 5 = 0) And (.Value > 0) And (.Value < 36) Then
> CopyAggregate
> End With
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Jim Tibbetts" wrote:
>
> > 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.
> > --
> > Jim T