Select Case Problem

[Guest]

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

 

[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

 

[Guest]

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.

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

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