Macro in Excel

Format all the relevant cells as follows:

With the column of Dates selected,
1. go to Format / Cells / Custom
2. type yyyymmdd (this is the code to enter your date as 20031203)
3. voila!

Hope this helps

Mary A. Lindsey

Open the spreadsheet you wish to format
Go to Tools>Macro>New Macro
Window"REcord MAcro" insert the Name of your choice. Note do leave any Blanks or use funny symbols.
Go to " Store Macro In" NEW Workbook" clikc OK

You will then see a small toollbar with a big dot appear symbolising stop Macro.
You then proceed to format as required, when you finnished your formatting process, ress the big dot to stop the Macro recorder. Save the Workbook under a name that will let you ID the macro.

You then can proceed the create a button on your Tool bar and linking this button to the macro. Everytime, you need to format your clients Worksheet all you need to do is click the button.

If you want to know how to create a button and link this, send me an e-mail

Next, comes the complex part.

In client's spreadsheet, for a single row, I need to check if cell G11 value is "-", if yes, ignore, else capture value, store in an array (perhaps call it Bin A).

Check if cell H11 is "-", if yes, ignore, else capture value and store in the same array (This time in Bin B?).

Continue the checking and storing of values until column O. All the values stored are date values from clients spreadsheet. The format of the date is 12-Mar or 16-Jan style. I need toconvert all these dates capture and convert them into my date format. ie: 200303120000 or 200301160000, etc..

After converting them, I need them to be re-arranged according to the earliest date first on the list.

When they are sorted out chronologically, the earliest date should be captured and check where the cell is under which column. Example: if there are three columns. A, B and C. Cell A7, B7, and C7 all have different names of places. (Let them be Florida, Texas, Michigan). If earliest date is found in B15, then the date belongs to column B (in this example, it'll be Texas). Capture the value of Texas and telly against a seperate list of index codes. Maybe Texas equals TX in the list. So, I need to input TX in my own version of spreadsheet. Followed by entering the new date format into my version.

Repeat the checking of the next date for position of which column it is under. As well as converting and inputing them into my version. This should be done continuously until all the dates captured in array are checked.

I hope I'm making sense. Pls revert for any questions. I can send a sample pair of sample files for your review. Or perhaps I could do screen capture and make comments on it to illustrate my requirements. Its possible even when I am unable to explain in person.

Pls advise. Thank you for looking into this.

This may be much easier solved if you can do it in Access.

It might be easier solved in Access, as mentioned. However, it can be done in Excel. I have some code you can look at - e-mail me and let me know and I'll send it over.

Point value changed by question poster.

This question was closed by the author