Excel VBA problem

[Mark1ace1]

Being new to Excel and only just getting around formula's, I would hav
no idea about how to write a simple VBA program. I was told the bes
place to go would be this excellent forum (Hint!!!!)...My problem i
this..

I have a DVD database with three worksheets. First one is calle
Movies, 2nd Customer Info and third customers.

Basically in Movies, I have name of movie and Ratings etc with cus
name. For instance Rating is in G9:G64), Customer name in J9:J64..No
what I want to do is if a customer tries to rent a movie and it is R1
or R15 depending on Age..The vba would check sheet3/customers for thei
age and date of birth and if that DOB is under 18 a message woul
display that person is under age and not allowed to rent movi
parse...I have tried Data Validation but that only brings up messag
when something is inputted into a cell, for instance I tried to creat
a cell which linked to DOB and if it brought back under 18 it woul
show message but Data validation never worked...Hope this message i
not too long...

Mar

 

[John]

Mark it sounds like you could accomplish this via
a vlookup to bring the age of the customer back to the
movies sheet, and the use an if(age<18,"warning","ok to
rent") or something like that.

The vlookup lets you find an entry in the left hand column
of a database and get an entry on that row.

John

 

[Bob Phillips]

You need a VLOOKUP into the movies database to get the movies details and
another into Customer to get DoB, and use a simple IF to check if okay. You
could highlight discrepancies with Data Validation of conditional
formatting.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark1ace1]

I tried that, but the age returns 12/01/00, and not sure how to add th
IF statement... I tried to do a VBA but was not sure how to list on
column...doing my head in ...
Mar

 

[Bob Phillips]

Mark,

Let's assume that the customer data is structured name in column A, DoB in
column B, then the date tests are


=IF(AND(G9="R18",DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))<VLOOKUP(
J9,'Customer'!A1:B100,2,FALSE),"Ok","Reject")

and


=IF(AND(G9="R15",DATE(YEAR(TODAY())-15,MONTH(TODAY()),DAY(TODAY()))<VLOOKUP(
J9,'Customer'!A1:B100,2,FALSE),"Ok","Reject")

Best way would be to have coilumns with intermeditae resluts and then test
these on the master sheet.

If you want to send me the workbook, I'll take a look for you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark1ace1]

I would love to email you, so that you can take a look..I was going t
try a message box, but ain't great with VB

Mar

 

[Mark1ace1]

Tried that formula no go...Now could it be as my dates are in Englis
date system and not the American one?

Mar

 

[Mark1ace1]

Nope tried changing to American format and still not working :