Problems with a case...select function

Hi,

I'm writing some macros for use in a game that I play. Apparently I've neglected something or there is a dumb mistake in my code. I'd appreciate any help in debugging it. I use Excel 2003 in WinXP (latest service packs and updates).

The intent of the macro is to take a string as input (dettype) to a case... select series. The dettype input is from a drop down list that is passed onto the current worksheet from another page and then the output of the resulting number of units for that option (grnnum) is supposed to be displayed in the cell.

As an example of the intended code implementation and desired results, if I enter a value of Cavalry from cell A1 into the function 'greens' in cell A2 I want to see a value of 2 returned (grnnum = 2) in cell A2.

The code that I have so far returns a value of zero (0), regardless of the value of dettype.

I'm not sure why.

Thanks in advance for any help!

Merry Christmas,

Chris

Public Function greens(dettype As String) As Variant
Dim grnnum As Variant
'------------------------
'Logic case selections for DetType results
    If dettype = " " Then
        grnnum = "Select a Detachment Type"
    Else
        Select Case dettype
'--------------------
            Case Is = "Cavalry"
                grnnum = 2
            Case Is = "Command"
                grnnum = 0
            Case Is = "Experimental"
                grnnum = 2
            Case Is = "Infantry"
                grnnum = 3
            Case Is = "Motorized Infantry"
                grnnum = 3
            Case Is = "Occult"
                grnnum = 2
            Case Is = "Reconnaisance"
                grnnum = 2
            Case Is = "Support"
                grnnum = 2
            Case Is = "Tank"
                grnnum = 2
            Case Is = "Veteran"
                grnnum = 0
            Case Else
                grnnum = 0
'--------------------
            End Select
    End If
End Function

Hi AbbysDad. Welcome. Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used. (Like I did below...)


This is how I would write your function. Zero is implied and those values can be exlcuded.

Option Explicit
Option Compare Text

Public Function GREENS(dettype As Range)
If dettype.Cells.Count > 1 Then
    GREENS = "one cell only"
    Exit Function
ElseIf Trim(dettype) = vbNullString Or dettype = " " Then
    GREENS = "Select a Detachment Type"
    Exit Function
Else
    Select Case dettype.Value
        Case "Cavalry", "Experimental", "Occult", "Reconnaisance", "Support", "Tank"
            GREENS = 2
        Case "Infantry", "Motorized Infantry"
            GREENS = 3
    End Select
End If
End Function

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)