Wasn't there an INLIST function at one time?

Posted on 2009-12-18
Last Modified: 2012-05-08
For some reason I seem to remember some function that allowed you to test if a string was in a list of specific values.  I'm working in Excel 2007 VBA...

My code looks like this

If Mid(txtSN, 4, 2) <> "01" And Mid(txtSN, 4, 2) <> "02" And Mid(txtSN, 4, 2) <> "03" And Mid(txtSN, 4, 2) <> "04" And Mid(txtSN, 4, 2) <> "05" And Mid(txtSN, 4, 2) <> "06" And Mid(txtSN, 4, 2) <> "07" And Mid(txtSN, 4, 2) <> "08" And Mid(txtSN, 4, 2) <> "09" And Mid(txtSN, 4, 2) <> "10" And Mid(txtSN, 4, 2) <> "11" And Mid(txtSN, 4, 2) <> "12" Then
            MsgBox "Serial # not in correct format, digits in positions 5-6 should be 01-12, YY-0MM-###"

And I wanted to write sometime like this
If INLIST(mid(txtSN,4,2), "01,02,03,04,05,06,07,08,09,10,11,12" then  blah

But obviously that won't compute.

Anyone remember and how would I write it?  I'm not looking for custom code, just a canned function.

Question by:RichNH
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    LVL 92

    Accepted Solution

    Hello RichNH,

    Another alternative...

    If InStr(1, "," & Mid(txtSN, 4, 2) & ",", ",01,02,03,04,05,06,07,08,09,10,11,12,") > 0 Then ...


    LVL 1

    Author Closing Comment

    That was what I was looking for, only thing is that the parms were backwards,  The larger string needs to be first and the sub string needs to be second.  Other than that, it works like a charm.  Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article will show you how to use shortcut menus in the Access run-time environment.
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now