atw: Re: VBA Select Case Statement question. [SEC=UNCLASSIFIED]
- From: Howard.Silcock@xxxxxxxxxxx
- To: austechwriter@xxxxxxxxxxxxx
- Date: Thu, 23 Aug 2007 13:06:09 +1000
Hi Bruce
When you want to start a Select... with the a statement
Select Case priority
VBA expects each of the following Case statements to specify a possible
value (or a range of values) for priority. So you can have Case
statements like
Case "Priority 1"
introducing actions to take place when priority = "Priority 1",
or (if priority were a numerical variable)
Case 1 To 4
introducing actions to take place when 1 <= priority <= 4, or
Case Is <= 10 (the Is operator is to express open-ended ranges)
introducing actions to take place when priority <= 10.
So somehow you'll have to sum up the cases you want to distinguish in
terms of a single variable, or else just use something like
Case "Priority 1"
If (tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.166664") then
tempArray(i, 3) = "1"
End if
Case "Priority 2"
If (tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.333328") Then
tempArray(i, 3) ..
End If
etc
Hope that helps
Howard
Howard Silcock
Technical Writer
Zare Pty Ltd
Ph 02 6261 2073
Fax 02 6112 2073
"
bruce.ashley@xxxxxxxx
Sent by: austechwriter-bounce@xxxxxxxxxxxxx
23/08/2007 12:31 PM
Please respond to
austechwriter@xxxxxxxxxxxxx
To
austechwriter@xxxxxxxxxxxxx
cc
Subject
atw: VBA Select Case Statement question. [SEC=UNCLASSIFIED]
Hi everyone.
Using VBA (via Excel 2003), I'm having some issues with a Select Case
statement I'm trying to put together.
In the past I have never had to test for multiple expressions of the type
shown below and have come across what appears to be an annoying limitation
with the validation of expressions (but I'm hoping someone out there will
advise otherwise).
Only the first expression is validated so the second and third ones are
ignored.
As an 'if else' statement, these multiple expressions work fine but I
cannot find out how to validate the Case statement equivalents as follows:
Select Case priority
Case Priority = "Priority 1" And _
(tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.166664")
tempArray(i, 3) = "1"
Case Priority = "Priority 2" And _
(tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.333328")
tempArray(i, 3) ... etc
I've tried many variations such as: Case "Priority 1", (tempArray(i, 2) >
"0") , (tempArray(i, 2) <= "0.166664") etc but to no avail.
It appears that you cannot add variables to the Select statement ie
Select Case (priority, tempArray).
Anyone know what the rules are here (I have no reference material other
than the Web and that has been of little help today)?
TIA.
Cheers,
Bruce Ashley
----------------------------------
MYOB Australia
bruce.ashley@xxxxxxxx
Ext 7160
----------------------------------
Other related posts:
