Select Case, Less and Greater than?

I am trying to get my code to take a value and work out which actions to do based on where the number is situated in the ranges.

a = Range("B3").Select

Select Case a
Case Is > 0 < 9
'do stuff
MsgBox ("less than 10")
Case Is > 10 < 999
'do stuff
MsgBox ("less than 100")
Case Is > 1000 < 40000
'do stuff
MsgBox ("less than 1000")
End Select

Only problem is, the code doesnt work, and neither does IF statements. What am I doing wrong?!

Thank you

the HELP file index for case would give a good example
Sub selectgreaterthan()
Select Case Range("b3")
Case 1 To 9
MsgBox "1"
Case 10 To 999
MsgBox "10"
'etc
Case Else
End Select
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Sami82" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to get my code to take a value and work out which actions to
> do based on where the number is situated in the ranges.
>
> a = Range("B3").Select
>
> Select Case a
> Case Is > 0 < 9
> 'do stuff
> MsgBox ("less than 10")
> Case Is > 10 < 999
> 'do stuff
> MsgBox ("less than 100")
> Case Is > 1000 < 40000
> 'do stuff
> MsgBox ("less than 1000")
> End Select
>
> Only problem is, the code doesnt work, and neither does IF statements.
> What am I doing wrong?!
>
> Thank you
>
>
> --
> Sami82
> ------------------------------------------------------------------------
> Sami82's Profile:
http://www.excelforum.com/member.php...o&userid=27111
> View this thread: http://www.excelforum.com/showthread...hreadid=475351
>

You criteria is basically a filter, so you stop as soon as you satisfy a
condition. Therefore you don't need to specify a lower bound:

Sub testCase()
i = 100

Select Case i
Case Is < 10
MsgBox "< 10"
Case Is < 100
MsgBox ">=10 and < 100"
Case Is < 1000
MsgBox " >= 100 and < 1000"
Case Is < 10000
MsgBox " >= 1000 and < 10000"
Case Else
MsgBox " >= 10000"
End Select
End Sub

as an example.

--
Regards,
Tom Ogilvy

"Sami82" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to get my code to take a value and work out which actions to
> do based on where the number is situated in the ranges.
>
> a = Range("B3").Select
>
> Select Case a
> Case Is > 0 < 9
> 'do stuff
> MsgBox ("less than 10")
> Case Is > 10 < 999
> 'do stuff
> MsgBox ("less than 100")
> Case Is > 1000 < 40000
> 'do stuff
> MsgBox ("less than 1000")
> End Select
>
> Only problem is, the code doesnt work, and neither does IF statements.
> What am I doing wrong?!
>
> Thank you
>
>
> --
> Sami82
> ------------------------------------------------------------------------
> Sami82's Profile:
http://www.excelforum.com/member.php...o&userid=27111
> View this thread: http://www.excelforum.com/showthread...hreadid=475351
>

Just an added thought.
That would work for integers

Sub selectgreaterthan()
Select Case 9.5
Case 1 To 9
MsgBox "1"
Case 10 To 999
MsgBox "10"
Case Else
MsgBox "Error"
End Select
End Sub

gives error.

--
Regards,
Tom Ogilvy

"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> the HELP file index for case would give a good example
> Sub selectgreaterthan()
> Select Case Range("b3")
> Case 1 To 9
> MsgBox "1"
> Case 10 To 999
> MsgBox "10"
> 'etc
> Case Else
> End Select
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Sami82" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I am trying to get my code to take a value and work out which actions to
> > do based on where the number is situated in the ranges.
> >
> > a = Range("B3").Select
> >
> > Select Case a
> > Case Is > 0 < 9
> > 'do stuff
> > MsgBox ("less than 10")
> > Case Is > 10 < 999
> > 'do stuff
> > MsgBox ("less than 100")
> > Case Is > 1000 < 40000
> > 'do stuff
> > MsgBox ("less than 1000")
> > End Select
> >
> > Only problem is, the code doesnt work, and neither does IF statements.
> > What am I doing wrong?!
> >
> > Thank you
> >
> >
> > --
> > Sami82
> > ------------------------------------------------------------------------
> > Sami82's Profile:
> http://www.excelforum.com/member.php...o&userid=27111
> > View this thread:
http://www.excelforum.com/showthread...hreadid=475351
> >
>
>

Good morning Tom,

OK 1 to 10 or <10 as you suggested.

--
Don Guillett
SalesAid Software
[email protected]
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Just an added thought.
> That would work for integers
>
> Sub selectgreaterthan()
> Select Case 9.5
> Case 1 To 9
> MsgBox "1"
> Case 10 To 999
> MsgBox "10"
> Case Else
> MsgBox "Error"
> End Select
> End Sub
>
> gives error.
>
> --
> Regards,
> Tom Ogilvy
>
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
> > the HELP file index for case would give a good example
> > Sub selectgreaterthan()
> > Select Case Range("b3")
> > Case 1 To 9
> > MsgBox "1"
> > Case 10 To 999
> > MsgBox "10"
> > 'etc
> > Case Else
> > End Select
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Sami82" <[email protected]> wrote in
> > message news:[email protected]...
> > >
> > > I am trying to get my code to take a value and work out which actions
to
> > > do based on where the number is situated in the ranges.
> > >
> > > a = Range("B3").Select
> > >
> > > Select Case a
> > > Case Is > 0 < 9
> > > 'do stuff
> > > MsgBox ("less than 10")
> > > Case Is > 10 < 999
> > > 'do stuff
> > > MsgBox ("less than 100")
> > > Case Is > 1000 < 40000
> > > 'do stuff
> > > MsgBox ("less than 1000")
> > > End Select
> > >
> > > Only problem is, the code doesnt work, and neither does IF statements.
> > > What am I doing wrong?!
> > >
> > > Thank you
> > >
> > >
> > > --
> > > Sami82
> >
> ------------------------------------------------------------------------
> > > Sami82's Profile:
> > http://www.excelforum.com/member.php...o&userid=27111
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=475351
> > >
> >
> >
>
>

Sami82
Are you declaring your variables ?
Dim a As ????

NickHK

"Sami82" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to get my code to take a value and work out which actions to
> do based on where the number is situated in the ranges.
>
> a = Range("B3").Select
>
> Select Case a
> Case Is > 0 < 9
> 'do stuff
> MsgBox ("less than 10")
> Case Is > 10 < 999
> 'do stuff
> MsgBox ("less than 100")
> Case Is > 1000 < 40000
> 'do stuff
> MsgBox ("less than 1000")
> End Select
>
> Only problem is, the code doesnt work, and neither does IF statements.
> What am I doing wrong?!
>
> Thank you
>
>
> --
> Sami82
> ------------------------------------------------------------------------
> Sami82's Profile:
http://www.excelforum.com/member.php...o&userid=27111
> View this thread: http://www.excelforum.com/showthread...hreadid=475351
>

a = Range("B3").Select will give variable a the value of TRUE or FALSE, not
the value of cell B3!
Try
a=Range("B3").Value

Bob Umlas
Excel MVP

"NickHK" <[email protected]> wrote in message
news:e$at2%[email protected]...
> Sami82
> Are you declaring your variables ?
> Dim a As ????
>
> NickHK
>
> "Sami82" <[email protected]> wrote in
> message news:[email protected]...
>>
>> I am trying to get my code to take a value and work out which actions to
>> do based on where the number is situated in the ranges.
>>
>> a = Range("B3").Select
>>
>> Select Case a
>> Case Is > 0 < 9
>> 'do stuff
>> MsgBox ("less than 10")
>> Case Is > 10 < 999
>> 'do stuff
>> MsgBox ("less than 100")
>> Case Is > 1000 < 40000
>> 'do stuff
>> MsgBox ("less than 1000")
>> End Select
>>
>> Only problem is, the code doesnt work, and neither does IF statements.
>> What am I doing wrong?!
>>
>> Thank you
>>
>>
>> --
>> Sami82
>> ------------------------------------------------------------------------
>> Sami82's Profile:
> http://www.excelforum.com/member.php...o&userid=27111
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=475351
>>
>
>