Easier way to write this

[vonoventwin]

Is there any easier way so next time I don't have to write this??


Public Function Category(Pool)

'Custom Function to determine ANG functional categories

If Pool = "510" Then
Category = "Suffolk Environmental Payroll"

ElseIf Pool = "520" Then
Category = "Suffolk Security Police"

ElseIf Pool = "530" Then
Category = "Suffolk Fire Department"

ElseIf Pool = "550" Then
Category = "Suffolk State Regulars"

ElseIf Pool = "556" Then
Category = "Suffolk Real Property Manager"

ElseIf Pool = "555" Or Pool = "556" Then
Category = "Suffolk Hourly/Snow Workers"

ElseIf Pool = "512" Then
Category = "Stratton Environmental Payroll"

ElseIf Pool = "522" Then
Category = "Stratton Security Police"

ElseIf Pool = "532" Then
Category = "Stratton Fire Department"

ElseIf Pool = "560" Then
Category = "Stratton State Regulars"

ElseIf Pool = "567" Then
Category = "Stratton Real Property Manager"

ElseIf Pool = "564" Then
Category = "Stratton Hourly/Snow Workers"

ElseIf Pool = "514" Then
Category = "Niagara Environmental Payroll"

ElseIf Pool = "524" Then
Category = "Niagara Security Police"

ElseIf Pool = "570" Then
Category = "Niagara State Regulars"

ElseIf Pool = "576" Then
Category = "Niagara Real Property Manager"

ElseIf Pool = "574" Then
Category = "Niagara Hourly/Snow Workers"

ElseIf Pool = "516" Then
Category = "Syracuse Environmental Payroll"

ElseIf Pool = "526" Then
Category = "Syracuse Security Police"

ElseIf Pool = "534" Then
Category = "Syracuse Fire Department"

ElseIf Pool = "580" Then
Category = "Syracuse State Regulars"

ElseIf Pool = "586" Then
Category = "Syracuse Real Property Manager"

ElseIf Pool = "584" Or Pool = "585" Then
Category = "Syracuse Hourly/Snow Workers"

ElseIf Pool = "518" Then
Category = "Stewart Environmental Payroll"

ElseIf Pool = "528" Then
Category = "Stewart Security Police"

ElseIf Pool = "536" Then
Category = "Stewart Fire Department"

ElseIf Pool = "590" Then
Category = "Stewart State Regulars"

ElseIf Pool = "596" Then
Category = "Syracuse Real Property Manager"

ElseIf Pool = "594" Or Pool = "595" Then
Category = "Syracuse Hourly/Snow Workers"

Else
Category = "Undefined"

End If

End Function

[DKenny]

You can use a SELECT CASE statement which will greatly simplify this.
Have a look at my post in the following thread.

http://www.vbforums.com/showthread.php?t=376545

[vonoventwin]

But every number gets a different word returned. So how does SELECT CASE simply this??

[DKenny]

Good point :blush:

What app are you using?

[vonoventwin]

Excel

[DKenny]

OK
One way to do this would be to add a worksheet and creat a 2 column list with the codes in column "A" and the descriptions in column "B". You could then use the vlookup function to return the correct description.

(You will need to change the sheet number in the following to reflect the sheet that has your list)

Function Category(Pool)
Category = Application.WorksheetFunction.VLookup(Pool, Worksheets(1).Range("$A:$B"), 2, 0)
End Function

[BobTheBuilder.]

Check out this (http://www.vbforums.com/showthread.php?p=2270628#post2270628) post bye eyeRMonkey. You may be able to adapt this to what you want by creating an enum. looping through it each time, then i = the value you are searching for then just display the name.

[si_the_geek]

Actually the Select Case does make it shorter, quicker, and easier to read, eg:

Public Function Category(Pool)

'Custom Function to determine ANG functional categories

Select Case Pool
Case "510": Category = "Suffolk Environmental Payroll"
Case "520": Category = "Suffolk Security Police"
Case "530": Category = "Suffolk Fire Department"
Case "550": Category = "Suffolk State Regulars"
Case "556": Category = "Suffolk Real Property Manager"
Case "555", "556": Category = "Suffolk Hourly/Snow Workers"
Case "512": Category = "Stratton Environmental Payroll"
Case "522": Category = "Stratton Security Police"
....
Case Else: Category = "Undefined"
End Select

End Function
By the way, "556" will never be seen as "Suffolk Hourly/Snow Workers", as "556" is the value for "Suffolk Real Property Manager".

I think the VLookup option would be a good one, but it needs to take these double-values into account too. Would it just mean two lines for "Suffolk Hourly/Snow Workers"?