Select Case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spencer.Sadkin@gmail.com - 22 Jan 2008 17:47 GMT

Hi,
I am having some trouble with Select Case. I have a bunch of ifelse
statements that would be run faster as a select case, but i cant get
the syntax. I want something like:

Function range(incw) as string

Select Case incw
Case is 0
range="None"
Case is >0 And <=5 ' This is the part im having
trouble with
range="Moderate"
Case is >5
range="Extreme"
End Select
End Function

Thanks

Reply to this Message

JP - 22 Jan 2008 17:56 GMT

Try this:

Function range(incw) As String

Select Case incw
Case 0
range = "None"
Case incw > 0 And incw <= 5
range = "Moderate"
Case Is > 5
range = "Extreme"
End Select
End Function

HTH,
JP

On Jan 22, 12:47 pm, Spencer.Sad...@gmail.com wrote:

> Hi,
> I am having some trouble with Select Case. I have a bunch of ifelse
[quoted text clipped - 15 lines]
>
> Thanks

Reply to this Message

Per Jessen - 22 Jan 2008 17:59 GMT

On 22 Jan., 18:47, Spencer.Sad...@gmail.com wrote:

> Hi,
> I am having some trouble with Select Case. I have a bunch of ifelse
[quoted text clipped - 15 lines]
>
> Thanks

Hi

Function range(incw As Long) As String

Select Case incw
Case Is = 0
range = "None"
Case Is > 5
range = "Extreme"
Case Else
range = "Moderate"
End Select
End Function

BTW: I wouldn't use range as function name as it also refers to an
object in VBA

Regards,

Per

Reply to this Message

JP - 22 Jan 2008 18:28 GMT

Tell me about it, I pasted the function into my VBE and it
(temporarily) broke all my macros! ;-)

--JP

> BTW: I wouldn't use range as function name as it also refers to an
> object in VBA
>
> Regards,
>
> Per

Reply to this Message

Don Guillett - 22 Jan 2008 18:43 GMT

Select Case incw
Case is >5:s="Extreme"
Case is >0:s="M"
case else:x="N"
end select
mr=x

> range="Extreme

> Case is 0
> range="None"
[quoted text clipped - 4 lines]
> range="Extreme"
> End Select

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
> I am having some trouble with Select Case. I have a bunch of ifelse
[quoted text clipped - 15 lines]
>
> Thanks

Reply to this Message

Spencer.Sadkin@gmail.com - 22 Jan 2008 19:02 GMT

> Select Case incw
> Case is >5:s="Extreme"
[quoted text clipped - 40 lines]
>
> > Thanks

ha ha sorry about the range object name, thats not actually the name
of my function nor is the code what my function does, its just a
simplified version. I will Try JP's suggestion again but i think i
tried it earlier and it did not work, didnt really understand Don's
coding, is that VBA?

Reply to this Message

JP - 22 Jan 2008 19:35 GMT

Yes, he just grouped the "Case" statements on one line. You can do
that by using ":"

HTH,
JP

On Jan 22, 2:02 pm, Spencer.Sad...@gmail.com wrote:

> > Select Case incw
> > Case is >5:s="Extreme"
[quoted text clipped - 8 lines]
> tried it earlier and it did not work, didnt really understand Don's
> coding, is that VBA?

Reply to this Message

Don Guillett - 22 Jan 2008 19:48 GMT

Correction and clarifications

Sub docase()
If Not IsNumeric(ActiveCell) Then Exit Sub
Select Case ActiveCell
Case Is > 5: s = "Extreme"
Case Is > 0: s = "M"
Case Else: s = "N"
End Select
mr = s
MsgBox mr
End Sub

Function mc(c)'c in the cell such as a1
If IsNumeric(c) Then
Select Case c
Case Is > 5: s = "Extreme"
Case Is > 0: s = "M"
Case Else: s = "N"
End Select
mc = s
End If
End Function

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Select Case incw
> Case is >5:s="Extreme"
[quoted text clipped - 33 lines]
>>
>> Thanks

Reply to this Message