Help with Case Please

[Guest]

Why am I getting an answer of 1 everytime? The function seems to be skipping
the Case programming. What do I need to do to fix this?

Function TabeGECol(Level, Form) As Integer
Dim ColNum As Integer
ColNum = 1
Select Case Level
Case Level = "L"
ColNum = 2
Case Level = "E"
ColNum = 10
Case Level = "M"
ColNum = 18
Case Level = "D"
ColNum = 26
Case Level = "A"
ColNum = 34
End Select
Select Case Form
Case Form = 7
ColNum = ColNum + 0
Case Form = 8
ColNum = ColNum + 2
Case Form = 9
ColNum = ColNum + 4
Case Form = 10
ColNum = ColNum + 6
End Select
TabeGECol = ColNum
End Function

 

[Dave Peterson]

Maybe you're not passing a nice Level value to the function?????

Or maybe you're passing a lower case letter????

Select Case ucase(Level)

 

[RB Smissaert]

Try this:

Function TabeGECol(Level As String, Form As Integer) As Integer

Dim ColNum As Integer

ColNum = 1

Select Case Level
Case "L"
ColNum = 2
Case "E"
ColNum = 10
Case "M"
ColNum = 18
Case "D"
ColNum = 26
Case "A"
ColNum = 34
End Select

Select Case Form
Case 7
ColNum = ColNum + 0
Case 8
ColNum = ColNum + 2
Case 9
ColNum = ColNum + 4
Case 10
ColNum = ColNum + 6
End Select

TabeGECol = ColNum

End Function


RBS

 

[Guest]

Function TabeGECol(Level, Form) As Integer
Dim ColNum As Integer
ColNum = 1
Select Case Level
Case "L"
ColNum = 2
Case "E"
ColNum = 10
Case "M"
ColNum = 18
Case "D"
ColNum = 26
Case "A"
ColNum = 34
End Select
Select Case Form
Case 7
ColNum = ColNum + 0
Case 8
ColNum = ColNum + 2
Case 9
ColNum = ColNum + 4
Case 10
ColNum = ColNum + 6
End Select

TabeGECol = ColNum
End Function

 

[David McRitchie]

In general macro comparisons are case sensitive,
and worksheet formulas are case in-sensitive. See
http://www.mvps.org/dmcritchie/excel/strings.htm#sensitivity

Select Case UCase(Level)

 

[Guest]

It probably has to do with the parameters you're passing in. Try this. Put
a Case Else statement as the last Case in each of your selects (a good
practice anyway).
You might try to put a Stop as the statement in the case else. Your code
will probably stop there. You can then use the immediate window to find out
what the value of Level is.

 

[Guest]

Thanks! That did it. ...so simiple

 

[Dana DeLouis]

Just something different...

Function TabeGECol(Level, Form) As Integer
Dim ColNum As Long

ColNum = 1 'Default

Select Case UCase(Level)
Case "L", "E", "M", "D", "A"
' { 2, 10, 18, 26, 34}
ColNum = 429295654 Mod Asc(UCase(Level))
End Select

Select Case Form
Case 7, 8, 9, 10
ColNum = ColNum + 2 * Form - 14
End Select
TabeGECol = ColNum
End Function

 

[Guest]

You are very welcome