Quote:
Originally Posted by Niclasfa
While mail merge would probably be the best solution and I trust your knowledge. I have been told by the admin manager that a macro is required. I can't unfortunately make any changes to the outlined request as they come from a subsidiary company.
|
Perhaps, then, your company would prefer to pay someone to do this, rather than wasting the time of community volunteers. I'm disinclined to write code just to amuse people who aren't interested in an efficient workflow. It seems whoever specified this project is wedded to a process more than to an outcome.
That said, your WelcomeAboard template has a quite unnecessary userform and nothing to indicate whether or how the fields in it should be filled. I say unnecessary because, even with a macro, everything could be done in the body of the document, using content controls.
Presumably the data are meant to come from the workbook, but there's nothing to indicate how any given record from the hundreds (or more) in the workbook might be used to populate the userform. Do the people behind this imagine the userform is going to fill itself out programmatically, without human intervention?
Quote:
Originally Posted by Niclasfa
So in regards to the mcrValidate. I was given the following information.
In row 1 of sheet Combined the following column headings: Last name, First name, Gender, DOB, Address, Suburb, State, Postcode, Role, Category.
Standardise all fonts to plain Arial 12 pt.
The column Category in sheet Combined was not in the original states data. Its purpose is to record the age category for each member.
The various categories from Junior to Great Grand Master
Junior - Under 18
Senior - 18+
Master - 40+
Grand Master - 50+
Great Grand Master - 60+
For each member data, generate the age category for column Category. for each row subtract DOB from your Nationals date to find the member's expected age on the day of competition, then compare with the category table below. Write out the category data for the member in full.
|
That's all very well but, unless you know what the date of the competition will be when the macro is run (and you've provided nothing to indicate where that date might come from), that is impossible. Although cell B5 on the Summary sheet has a date for the next national competition, that cell has a formula (=TODAY()), which will simply update itself every day. to cope with that the macro has to inserts a formula into every cell in the Category column. Otherwise, the output could be wrong the following day... In any event, even if you use fixed date in cell B5 on the Summary sheet, a formula allows those calculations to be updated later if the date needs changing (e.g. if a new competition date is decided upon), whereas a result generated by a macro at run-time would be static.
Finally, the Category info isn't provided for in the WelcomeAboard template, so I'm not sure what the point of those calculations is.
Updated workbook attached.