Excel VBA problem

  • Thread starter Thread starter Mark1ace1
  • Start date Start date
M

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
 
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
 
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)
 
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
 
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)
 
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
 
Tried that formula no go...Now could it be as my dates are in Englis
date system and not the American one?

Mar
 
Back
Top