Nested Select Case

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got some code that doesn't work:

Public Function OConsolTest(Comp As Variant, Acct As Variant)

Select Case Comp
Case TDEL
Select Case Acct
Case 100000 To 150000
OConsolTest = "TDEL REVENUE"
Case Else
OConsolTest = "TDEL REVENUE ERROR"
End Select

Case Is <> TDEL
Select Case Acct
Case 100000 To 150000
OConsolTest = "OTHER REVENUE"
Case Else
OConsolText = "OTHER REVENUE ERROR"
End Select
End Select
End Function

I can never get this to handle the <> TDEL section of the code. What's wrong?
 
Kirk, the answer here is to use Case Else like you have in the nested select,
instead of Case Is <> TDEL

-Jack
 
I really to need first evaluate the company using the Comp variable. Within
each company is a set of Accounts (Acct variable) that need to be evaluated.
In reality, I have more than 2 companies, so I really need something that can
evaluate more than 2.

Therefore, if the company is TDEL, it should follow the first set of
instructions, if the company is XDEL, it should follow the next set of
instructions, and so on and so forth.
 
Public Function OConsolTest(Comp As String, Acct As Long)

Select Case Comp
Case "TDEL"
Select Case Acct
Case 100000 To 150000
OConsolTest = "TDEL REVENUE"
Case Else
OConsolTest = "TDEL REVENUE ERROR"
End Select

Case Is <> "TDEL"
Select Case Acct
Case 100000 To 150000
OConsolTest = "OTHER REVENUE"
Case Else
OConsolText = "OTHER REVENUE ERROR"
End Select
End Select
End Function


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I really to need first evaluate the company using the Comp variable.
Within
| each company is a set of Accounts (Acct variable) that need to be
evaluated.
| In reality, I have more than 2 companies, so I really need something that
can
| evaluate more than 2.
|
| Therefore, if the company is TDEL, it should follow the first set of
| instructions, if the company is XDEL, it should follow the next set of
| instructions, and so on and so forth.
|
|
| "Jack" wrote:
|
| > Kirk, the answer here is to use Case Else like you have in the nested
select,
| > instead of Case Is <> TDEL
| >
| > -Jack
| >
| >
| >
| > "Kirk P." wrote:
| >
| > > I've got some code that doesn't work:
| > >
| > > Public Function OConsolTest(Comp As Variant, Acct As Variant)
| > >
| > > Select Case Comp
| > > Case TDEL
| > > Select Case Acct
| > > Case 100000 To 150000
| > > OConsolTest = "TDEL REVENUE"
| > > Case Else
| > > OConsolTest = "TDEL REVENUE ERROR"
| > > End Select
| > >
| > > Case Is <> TDEL
| > > Select Case Acct
| > > Case 100000 To 150000
| > > OConsolTest = "OTHER REVENUE"
| > > Case Else
| > > OConsolText = "OTHER REVENUE ERROR"
| > > End Select
| > > End Select
| > > End Function
| > >
| > > I can never get this to handle the <> TDEL section of the code.
What's wrong?
| > >
| > >
 
Here's what happens when I plug this function into the immediate window.
First, I get an error message saying "ByRef Argument Type Mismatch". When I
change the variables to variants, the function runs, but produces the wrong
answer.

For example, if I type ?OConsolTest(TDEL,100000) in the Immediate window, it
returns "OTHER REVENUE", instead of the "TDEL REVENUE" I would expect.

Any ideas on this one?
 
Try calling call the function like;

OConsolTest("TDEL",100000)

instead since COMP is really a string..

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Here's what happens when I plug this function into the immediate window.
| First, I get an error message saying "ByRef Argument Type Mismatch". When
I
| change the variables to variants, the function runs, but produces the
wrong
| answer.
|
| For example, if I type ?OConsolTest(TDEL,100000) in the Immediate window,
it
| returns "OTHER REVENUE", instead of the "TDEL REVENUE" I would expect.
|
| Any ideas on this one?
 
You're welcome.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Yep, that did it. Thanks for the help!
|
|
| "Dave Patrick" wrote:
|
| > Try calling call the function like;
| >
| > OConsolTest("TDEL",100000)
| >
| > instead since COMP is really a string..
| >
| > --
| > Regards,
| >
| > Dave Patrick ....Please no email replies - reply in newsgroup.
| > Microsoft Certified Professional
| > Microsoft MVP [Windows]
| > http://www.microsoft.com/protect
 
Back
Top