Value finding in list

**peakymatt** · 05-10-2010, 10:03 PM

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.

**protonLeah** · 05-10-2010, 11:17 PM

HINT: Data Validation->List

**JBeaucaire** · 05-11-2010, 12:38 AM

Why are you doing this in programming? A simple formula in G6 and copied down:
=ISNUMBER(MATCH($F6,Fruit,0))

**peakymatt** · 05-11-2010, 12:39 AM

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.

**JBeaucaire** · 05-11-2010, 01:07 AM

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.

**peakymatt** · 05-11-2010, 01:41 AM

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