Checking Multiple Values Against Multiple Columns

**Maglor** · 09-22-2009, 09:23 AM

Hello all

,

I'm currently working on a bit of a database and I'm trying to get a userform to check if an entry already exists.

basically, column "a" is first name, column "b" is surname and "c" is date of birth.

I have a userform with 3 text boxes where this information can be entered but I'm a bit stumped

as to how to code it so that it checks for column "a" data until it finds a match then when it does it checks column "b" data and if it matches it checks column "c" for a match but if it doesnt it tries to find another match in column "a" until the end of the worksheet is reached.

I think it has something to do with loops and if statements in if statements but that's a bit beyond me at this point.

The reason for this is it will be possible for the same first name, last name and date of birth to appear multiple times throughout the database but unlikely for there to be a complete match of all 3 on the same row.

the last bit is relatively easy and i should be able to work out but i would like a msgbox to popup and say match found or not found depending on the case.

sorry for the wall of words and if you need any clarification please feel free to msg me or reply to thread.

Kind Regards
Dave

**MickG** · 09-22-2009, 10:06 AM

Hi, Perhaps something like this:-
Code combines the 3 text boxes and compares the resulting string against the Combined data in each row of Columns "A to C".
If match Found Msgbox Result shows Matching Line.
If No match, Msgbox = "No Match Found"

Please Login or Register  to view this content.

Regards Mick

**Maglor** · 09-22-2009, 08:08 PM

Hi Mick,

Thanks so much for your prompt response.

The code you have put in looks like it will do the trick but im trying to understand it and adapt it to use on another part of the database. It is the same information but the columns are different B, C and H respectively.

also in this part I would like the message box to display the value of column A instead of the row value.

if you or anyone else out there has the time to help me breakdown the code into terms i can understand a bit easier that would be greatly appreciated.

Thanks once again Mick your code solved my first problem

PS. is there a way to make the definition of each a bit slacker ie date might be entered into DB as dd/mm/yyyy or dd/mm/yy and first and surnames may or may not start with Capital Letters etc

Cheers

**Maglor** · 09-22-2009, 08:40 PM

ok, after a bit more tinkering and searching around i figured out the columns thing and the getting it to display column A in the text box now my only problem is the capital letters and date format if anyone out there can help with that it would be greatly appreciated.

Ill keep tinkering tho

For anyone else out there with a similar problem what i altered in the code was

Please Login or Register  to view this content.

changed rng value from A to B

Please Login or Register  to view this content.

Added a few more .next to get the range over to column H and added an offset to the message box line to move it back over to column A

it may not be the prettiest way to do it but it seems to be working