The Great Calendar Project Journey to a Solution
The Story of A Journey: The Journey Begins Phase I: Outlook to Excel Phase II: Cleaning Up the Excel Data Phase III: Call In the Experts Phase IV: Making It Goof-Proof Phase V: A Test Run and More Goof-Proofing Phase VI: Fine-Tuning Trouble In Paradise Credits and Thanks Home	PHASE IV: MAKING IT ALL GOOF-PROOF On October 14, Kitty’s mind wakes her up at 6:00 a.m. and suggests she try to make the macros more goof-proof so future users can’t botch things up by accident. Potential mistake #1: If the user doesn’t put his/her cursor in the first empty cell AFTER the data before he/she runs the macro GetData to autofill the empty dates, he/she is going to overwrite existing data and make a mess. Back to the Experts: Solution Title: Macro to go to first empty cell asked by prettykittyq on 10/14/2004 08:37AM EDT This solution was worth 200 Points and received a grade of A With loads of help from EE experts, I now have lots of macros to do lots of neat things. The user has to go to the first empty row (end of data) before he/she can run one of the macros. Is there any way to use VBA code to automatically to find and go to the Column A cell of the first empty row? If it helps, the first empty cell in Column A will get me there since Column A won't be empty unless the whole row is empty. The ultimate users of these macros tend to forget steps like that and make a mess of things. Thanks in advance. Kitty and her magical Calendar At 10:47 a.m., roos01 provides the solution: macro EmptyA Kitty makes a note in her instructions that users have to run EmptyA before GetDate. Potential mistake #2: If the user doesn’t sort the data by date and time before running macro CompareDates, the routine won’t work properly. Solution Title: Select range and sort on two fields with a macro asked by prettykittyq on 10/14/2004 08:42AM EDT This solution was worth 350 Points and received a grade of A My users now manually select a range made up of Column A through Column F (the number of rows is not known in advance). They then have to sort by Column A (and specify that the sort should treat months as months, not as text) and Column C. The worksheet does have a header row. Can this be automated via VBA code for them? Thanks. Kitty At 9:38 a.m., jeverist posts an answer: macro SortDateTime Kitty now has all the macros she needs to automate all the data clean-up tasks and has macros to keep the user from making too many mistakes. Kitty saves it all to her trusty memory stick and carts it off to the office. To her delight and relief, the macros run just fine from the office computer. Current time to produce the document: 35 minutes – down from 65 minutes. Kitty spends part of the day documenting the project and writing very clear instructions for the users. End of phase IV. Click here to go on to Phase V...	Some VBA Code: All Macros CompareDates DeleteFakeTimes Empty A ForgetThePast GetDate ImportCalendarData KillB ReplaceApos SaveMyFile (v. 1) SaveMyFile (v. 2) SortDateTime SuperMacro Other Stuff Calendar Template (Excel file) dbCalendar.mdb (Access database) Report Screenshot
A KittyTours website maintained by Jean K. Rosales, Ph.D. Comments, corrections, and suggestions should be sent to webmaster@kittytours.org © 2004 kittytours.org, All Rights Reserved