 |
|
Create
your own Macro's (Part 2) Macro's are a powerful tool within Excel that can allow you to perform complex tasks with the click of a button. A Macro is simply a series of commands recorded into a script that is executed when required. This month we develop a Macro that will split a list of Names into two (2) columns one for the First Name and one for the Last Name. This type of Macro can be very useful when trying to create Mailing lists from source data that is not in the right format. In next month's newsletter we will develop a Macro that helps you extend Excel's in-built sorting functions. If you have a Macro suggestion for inclusion then Contact Us and we will endeavour to include it in a future newsletter. Re-Format Customer Name Let's say that you have a list of Customer Names and you want to use the First Name and Last Name separately in a Mailout that you are preparing for your Company. Rather than going through each name in the list and manually splitting the Names into First and Last, we will build a simple Macro that will do this automatically. Note: The following steps have been developed for Excel 2003 users. Step 1 - Open a new Workbook and Paste into Sheet2 the list of customer names that you want to re-format. Insert a row at the start of that list and type the Column name "Combined Name" at the top of the list of customer names. Step 2 - Now select the cell containing the Column name "Combined Name" and then Select Insert -> Name -> Define. Type in the name "CombinedListStartHeading" and click OK. This will provide a reference point for our Macro when we start re-formatting the list. Step 3 - Select Tools -> Macro -> Visual Basic Editor. A new window will be opened that allows for the direct entry and creation of Macro's. Select the VBA Project of the Workbook you created in Step 1 above. Now Select Insert -> Module. A new Module will be created for the Workbook if this is the first Macro in the Workbook it will be called Module1 Step 4 - We are now ready to create our Re-formatting Macro. Right Click on Module1 and Select View Code. On the Right Hand Side of the Visual Basic Editor a blank Code window will be displayed. Step 5 - Copy the following Macro Code into the Visual Basic Editor, this will create the Macro within your Workbook. Sub ReFormatCustomerName() 'This Macro will split a Customer Name into First 'and Last Name where a space is found within the Name Dim myRange As Range Dim i As Integer Dim intSpacePosition As Integer On Error Resume Next Set myRange = Worksheets("Sheet2").Range("CombinedListStartHeading") i = 1 Do While myRange.Offset(i, 0).Value <> "" intSpacePosition = 0 myRange.Offset(i, 1).Value = myRange.Offset(i, 0).Value myRange.Offset(i, 2).Value = "" intSpacePosition = WorksheetFunction.Find(" ", myRange.Offset(i, 0).Value) If Err.Number = 0 Then myRange.Offset(i, 1).Value = Mid(myRange.Offset(i, 0).Value, 1, intSpacePosition) myRange.Offset(i, 2).Value = Mid(myRange.Offset(i, 0).Value, intSpacePosition + 1, Len(myRange.Offset(i, 0).Value)) End If i = i + 1 Loop End Sub Step 6 - Close the Visual Basic Editor Window and we will now run the Macro to re-format the Customer Name. Select Tools -> Macro -> Macros. Then Select the Macro we just created in Step 5 above 'ReFormatCustomerName', then Select Run.  This should then run the Macro and re-format the Customer Name into the First Name and Last Name Columns. The re-formatted list should look something like this sample.  If you received an error message when running the Macro check that you have setup the Customer list on "Sheet2" of the workbook and that the defined name is exactly the same as the one specified in Step2 above. If the Macro is still reporting errors then download our Sample Macro Workbook and see this Macro in action Click Here. In next month's newsletter we will develop another Macro that will enhance Excel's in-built sorting capability. The Macro will allow you to sort more than three (3) columns of data at the same time. If you have any questions or would like help in developing Macro's for your business then Contact Us and we will assist. If you are not yet a subscriber to the XLAutomation newsletter then Click Here to subscribe and receive guidance and tools that will increase your efficiency in using Microsoft Excel, Word and Powerpoint. |
STOP PRESS !!
March Competition Winner Announced.... Congratulations to "Kym" one of our new subscribers in March.....you have won 10 hours FREE Consulting from the XLAutomation Team Click Here for details on how Subscribers can enter the next competition for your chance to win more FREE consulting from XLAutomation team Get Free Advice... If you have a specific problem or area of Excel that you would like to see featured in next months Newsletter then let us know. Click Here to tell us what YOU need. Latest Offers "Free Consultation" Take advantage of our 1 hour free on-site consultation. We guarantee to provide advice that will improve the way you currently use your Microsoft® Office software. Click Here to book a session with one of our consultants. "Message Blaster" is here... We have listened to our clients needs and after much anticipation released our lastest exciting new product. Message Blaster provides "One Click" communication with your customers and contacts. Imagine a system that automatically sends SMS's and Emails, without manual intervention! Click Here to find out more about Message Blaster. To hear what our customers have to say about Message Blaster Click Here |
|
|