Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
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 -----Original Message----- Being new to Excel and only just getting around formula's, I would have no idea about how to write a simple VBA program. I was told the best place to go would be this excellent forum (Hint!!!!)...My problem is this.. I have a DVD database with three worksheets. First one is called Movies, 2nd Customer Info and third customers. Basically in Movies, I have name of movie and Ratings etc with cust name. For instance Rating is in G9:G64), Customer name in J9:J64..Now what I want to do is if a customer tries to rent a movie and it is R18 or R15 depending on Age..The vba would check sheet3/customers for their age and date of birth and if that DOB is under 18 a message would display that person is under age and not allowed to rent movie parse...I have tried Data Validation but that only brings up message when something is inputted into a cell, for instance I tried to create a cell which linked to DOB and if it brought back under 18 it would show message but Data validation never worked...Hope this message is not too long...:( Mark --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
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 " wrote in message ... Being new to Excel and only just getting around formula's, I would have no idea about how to write a simple VBA program. I was told the best place to go would be this excellent forum (Hint!!!!)...My problem is this.. I have a DVD database with three worksheets. First one is called Movies, 2nd Customer Info and third customers. Basically in Movies, I have name of movie and Ratings etc with cust name. For instance Rating is in G9:G64), Customer name in J9:J64..Now what I want to do is if a customer tries to rent a movie and it is R18 or R15 depending on Age..The vba would check sheet3/customers for their age and date of birth and if that DOB is under 18 a message would display that person is under age and not allowed to rent movie parse...I have tried Data Validation but that only brings up message when something is inputted into a cell, for instance I tried to create a cell which linked to DOB and if it brought back under 18 it would show message but Data validation never worked...Hope this message is not too long...:( Mark --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
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 " wrote in message ... I tried that, but the age returns 12/01/00, and not sure how to add the IF statement...:( I tried to do a VBA but was not sure how to list one column...doing my head in ... Mark --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
Tried that formula no go...Now could it be as my dates are in Englis
date system and not the American one? Mar -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA problem
Nope tried changing to American format and still not working :
-- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Problem viewing Excel 2003 Pivot Chart fields in Excel 2007 | Charts and Charting in Excel | |||
Weird problem with Excel 2000...Worksheets disappearing in a shared Excel file | Excel Discussion (Misc queries) | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Excel 97 chart opened in Excel 2003 - Source Data problem | Charts and Charting in Excel |