Nested Select Case Alternative

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Hello people,

I'm writing a custom function that checks three criteria and assigns a boolean value based on the outcome. At the moment I have nested Select Case statements to check these but I started wondering whether there was a better alternative.

as an example:
criteria 1 as a supplier name
criteria 2 as a product
criteria 3 as a date

if a particular product comes from supplier 1 or 2 then i'd set to true otherwise false.

I thought about putting together a table but there are instances where the flag is set to true only when it's between a specific date range so I wasn't sure how this could be incorporated.

Any ideas for alternatives I may not have thought of?

Thanks

Nick
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
If there is a specific order/precedence with your three conditions, I think you will be better off using VBA than trying to create some sort of lookup table.

Creating a lookup table works well if you only have one condition, but a lot of different posibilities under that one condition. If each of your conditions only has a few different possibilities, I would stick with the VBA.

That's just my personal preference anyway, FWIW...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,283
Office Version
  1. 365
Platform
  1. Windows
Nick

If you haven any current code for the function could you post it?
 

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
It may well be the most efficient in the end. Just seems really messy having so many select statements to go through.

Thanks

Nick
 

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Hi Norie,

here's what I have so far. I've had to change the variable names etc so if I've missed somethign and it no longer makes sense I apologise. I haven't included the date parts yet but will be soon.

Code:
Function MailoutInclude(sProductCode As String, sClientCompany As String, sRange As String, dAssistDate As Date) as Boolean



Select Case sClientCompany


Case "SUPPLIER1"

    Select Case sProductCode
        Case "L82", "R48", "R49", "N26", "T13", "T15", "T16", "T20", "R16", "R17", "R33", "R52", "M31", "N27", "R50", "L55", "L73"
        MailoutInclude = True
        Exit Function
    End Select



Case "SUPPLIER2"

    Select Case sProductCode
        Case "L82", "R48", "R49", "N25", "M29", "M30", "M33", "M34", "M36", "F23", "R37", "R48", "R49", "M14", "M47"
        MailoutInclude = True
        Exit Function
    End Select
        




Case "SUPPLIER3", "SUPPLIER4"

    Select Case sProductCode
        Case "R37", "R24", "N40"
        MailoutInclude = True
        Exit Function
        
        
    Case Else
        MailoutInclude = False
    
        
        Select Case sRange

            Case "LANLN"
                If sProductCode = "N15" Or sProductCode = "T11" Then
                    MailoutInclude = True
                    Exit Function
                End If
                

            Case "LANLP"
                If sProductCode = "T31" Or sProductCode = "R27" Then
                    MailoutInclude = True
                    Exit Function
                End If
            

            Case "J16"
                If sProductCode = "T20" Then
                    MailoutInclude = True
                    Exit Function
                End If
                

            Case "J06", "J17", "J16"
                If sProductCode = "T11" Then
                    MailoutInclude = True
                    Exit Function
                End If
                

            Case "LANRS"
                If sProductCode = "R13" Then
                    MailoutInclude = True
                    Exit Function
                End If
            

            End Select
            

        End Select
            

End Select



End Function

Thanks

Nick

P.S. is it not possible to make the code window wider and lose the bottom scroll bar?!?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top