+ Reply to Thread
Results 1 to 7 of 7

Value finding in list

  1. #1
    Registered User
    Join Date
    05-18-2008
    Posts
    54

    Value finding in list

    Hi All

    Having a little trouble with my vba. I am trying to find a value in a list of data dependant on whether that value exists in another list.

    I have attached an example of what I am trying to achieve. At the moment I can find a value in List one if I put that value (x) in my code. However, I want the value of (x) to be any value in List 2. If that makes any sense. List 1 contains a set list of values, whereas List 2 will change dependant the user.

    The result that I want is that if the any of the contents in list 2 don't exist then a msgbox will tell me.

    If somebody could help I would be very grateful.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,469

    Re: Value finding in list

    HINT: Data Validation->List
    Ben Van Johnson

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Value finding in list

    Why are you doing this in programming? A simple formula in G6 and copied down:
    =ISNUMBER(MATCH($F6,Fruit,0))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    05-18-2008
    Posts
    54

    Re: Value finding in list

    Thanks, but not really what I am after.

    Code has to check through the list on a user command. Data Validation doesn't do the job.

    To JBeaucaire

    I was doing it in code as the msgbox I need is actioned when a check is done once all user inputs have been entered. The user adds data over a couple of days to a different sheet which grabs a particular cell, which in turn builds List 2. At the end of each week all data is compiled and a button is clicked to check contents of List 2 against List 1. Then if there is an entry in List 2 that doesn't exist in List 1 then the MsgBox.
    Last edited by peakymatt; 05-11-2010 at 12:46 AM. Reason: Adding more detail

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Value finding in list

    How about this, it will check all the items in List 2 against List 1 and give you a single MsgBox at the end of the items NOT found.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-18-2008
    Posts
    54

    Re: Value finding in list

    That worked a treat. I never would have thought of that. I am still very new to VBA

    Thanks for your help. I will be able to adapt that to the real data now.

    Thanks

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Value finding in list

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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