Select Case Problem

K

Kirk P.

This Select Case statement isn't returing the desired results. I'm trying
for, in case 1, when the expression evaluates to True, return "Direct Ops
Expense". What am I doing wrong?

Function RowDesig2(AcctLvl1 As String, FuncGroup As String, Prod As String)

Select Case RowDesig2
Case [AcctLvl1] <> "Revenue" And [FuncGroup] = "Operations" And [Prod]
<> "999"
RowDesig2 = "DIRECT OPS EXPENSE"
Case [AcctLvl1] <> "Revenue" And [FuncGroup] = "Products" And [Prod] =
"999"
RowDesig2 = "INDIRECT OPS EXPENSE"
Case [AcctLvl1] <> "Revenue" And [FuncGroup] <> "Operations" And
[FuncGroup] <> "Products"
RowDesig2 = "OTHER SGA - SHARED SERVICES"
Case Else
RowDesig2 = "SOME OTHER DESIGNATION"
End Select

End Function
 
J

John Spencer

Generally case can only evaluate ONE variable.
How about rewriting the function as follows?

Function RowDesig2(AcctLvl1 As String, FuncGroup As String, Prod As String)

IF AcctLvl1 <> "Revenue" And _
FuncGroup = "Operations" And Prod <> "999" THEN
RowDesig2 = "DIRECT OPS EXPENSE"

ELSEIF AcctLvl1 <> "Revenue" And _
FuncGroup = "Products" And Prod = "999" THEN
RowDesig2 = "INDIRECT OPS EXPENSE"

ELSEIF AcctLvl1 <> "Revenue" And _
FuncGroup <> "Operations" And FuncGroup <> "Products" THEN
RowDesig2 = "OTHER SGA - SHARED SERVICES"

ELSE
RowDesig2 = "SOME OTHER DESIGNATION"
END IF

End Function
 
K

Kirk P.

I did have the function using the IF statement almost exactly as you
described. I guess I was under the impression that when using an IF
statement with multiple cases, then the SELECT CASE statement is optimal. I
don't have as much experience with the SELECT CASE, so I was just giving it a
try.

Thanks for the feedback! I'll just use the IF.

John Spencer said:
Generally case can only evaluate ONE variable.
How about rewriting the function as follows?

Function RowDesig2(AcctLvl1 As String, FuncGroup As String, Prod As String)

IF AcctLvl1 <> "Revenue" And _
FuncGroup = "Operations" And Prod <> "999" THEN
RowDesig2 = "DIRECT OPS EXPENSE"

ELSEIF AcctLvl1 <> "Revenue" And _
FuncGroup = "Products" And Prod = "999" THEN
RowDesig2 = "INDIRECT OPS EXPENSE"

ELSEIF AcctLvl1 <> "Revenue" And _
FuncGroup <> "Operations" And FuncGroup <> "Products" THEN
RowDesig2 = "OTHER SGA - SHARED SERVICES"

ELSE
RowDesig2 = "SOME OTHER DESIGNATION"
END IF

End Function

Kirk P. said:
This Select Case statement isn't returing the desired results. I'm trying
for, in case 1, when the expression evaluates to True, return "Direct Ops
Expense". What am I doing wrong?

Function RowDesig2(AcctLvl1 As String, FuncGroup As String, Prod As
String)

Select Case RowDesig2
Case [AcctLvl1] <> "Revenue" And [FuncGroup] = "Operations" And [Prod]
<> "999"
RowDesig2 = "DIRECT OPS EXPENSE"
Case [AcctLvl1] <> "Revenue" And [FuncGroup] = "Products" And [Prod] =
"999"
RowDesig2 = "INDIRECT OPS EXPENSE"
Case [AcctLvl1] <> "Revenue" And [FuncGroup] <> "Operations" And
[FuncGroup] <> "Products"
RowDesig2 = "OTHER SGA - SHARED SERVICES"
Case Else
RowDesig2 = "SOME OTHER DESIGNATION"
End Select

End Function
Click to expand...
Click to expand...
 
Top