+ Reply to Thread
Results 1 to 6 of 6

vba equivalent of SQL's IN function?

  1. #1
    Registered User
    Join Date
    07-18-2010
    Location
    Arlington, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    vba equivalent of SQL's IN function?

    Hi all,

    I'm new to vba programming (and new to this forum!). I do a little SQL and SAS programming though, and both of those languages have an "IN" function. To demonstrate:

    In SQL, I can code something like:
    SELECT row WHERE city IN(miami, dallas, LA, NY)

    Which can also be written:
    SELECT row WHERE city=miami or city=dallas or city=LA or city=NY

    Is there a VBA equivalent of the IN function? For instance, if I want to check if the active cell is in column 1,2,4, or 7, can I code something like:
    If ActiveCell.Column IN(1,2,4,7) Then (do something)?

    or do I have to do it the long way and code:
    If ActiveCell.Column = 1 OR ActiveCell.Column=2 OR ActiveCell.Column=4 OR ActiveCell.Column=7 Then (do something)

    Thanks!
    Last edited by larbadar; 07-24-2010 at 04:22 PM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: vba equivalent of SQL's IN function?

    Perhaps something like this....
    The program cycles through the cells of the selected range.
    Cell with values that match 1,2,4 or 7 are colored orange.

    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    07-18-2010
    Location
    Arlington, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba equivalent of SQL's IN function?

    Ron,

    That indeed got me what I was looking for! Thanks for the quick response! I modified it a wee bit to suit my taste. The code I'm using checks to see if the selected cell is in column 1,2,4, or 7, and if it's not, the user gets called a baboon. Now I can also easily modify the column numbers when needed.

    Please Login or Register  to view this content.
    Thanks again, Ron!
    Last edited by larbadar; 07-24-2010 at 04:24 PM.

  4. #4
    Registered User
    Join Date
    07-18-2010
    Location
    Arlington, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba equivalent of SQL's IN function?

    Well my modification didn't quite work. The way it's written, if the target cell is in column 1, then the user would not get a message box calling them a baboon. However, if the user selects a cell in column 2, 4, or 7, the user (incorrectly) gets the baboon message. In other words, the case statement only looks at the first value ("1") as the only way to avoid getting the baboon message. Anybody know why?

    In the meantime, I altered the code again and now it does what I want, but I still wonder why the code in my other post doesn't work.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: vba equivalent of SQL's IN function?

    The case statement is looking for the first matching item.
    When you use: Case Is <> 1, 2, 4, 7
    as soon as the column is NOT 1, that's considered a match.

    Does that help?

  6. #6
    Registered User
    Join Date
    07-18-2010
    Location
    Arlington, Va
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: vba equivalent of SQL's IN function?

    I got it. And really, using "=" instead of "<>" more closely resembles the IN(1,2,4,7) function I originally asked about anyway.

    I think I was trying to use "<>" to simulate a NOT IN(1,2,4,7) type of function, but I see it doesn't quite work that way.

    Makes sense now. Thanks Ron

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1