Yesterday I conducted an hour-long Webcast, “Macros for Budgeting & Corporate Accounting.” During the session, I covered one simple macro recording example — and then covered the four things you absolutely have to learn to create effective macros.

Basically, the Excel macro recorder will never record these four items for you. However, once you master the quartet of items, you can use a combination of the macro recorder and the four concepts to solve any problem in Excel. The Webcast ran five minutes long, and I did not get to address all the Q&A at the end. So for this week and next, I’ll present some macro examples in this column to answer the questions our Webcast attendees posed. (By the way, if you missed the Webcast, an archive will soon be available at CFO.com’s Webcast page.)

Question 1: Can You Use Text-to-Columns in a Macro?
Text-to-columns is an example of code that should be written with the macro recorder. Say that you download a file from the IT department every day. Headings appear in row 2, and the data starts in row 3. Column D contains a four-digit cost-center number, a dash, then an account number.

To start, you would like to split column D into two columns, and to further complicate the situation, there will be a different number of rows of data each day. There is also some additional data to the right of column D that you do not want to overwrite.

Here are the steps to create a flawless macro.

  1. Open today’s file in Excel.
  2. In the View tab, go to the Macros dropdown. Choose Record Macro.
  3. In the Record Macro dialog, enter a macro name of SplitColumnD. Type Shift+D in the Shortcut Key field. Open the Store Macro In dropdown and choose Personal Macro Workbook. Click OK.

The first several steps of the macro are recorded with relative recording turned off. These steps require the same actions to happen in the same place every day.

  1. Right-click on the “E” column heading and choose Insert. This will insert a blank column so that the text-to-columns operation does not overwrite the data to the right.
  2. Choose the newly blank cell E2 and type a heading such as ACCT.
  3. If needed, type a different heading in D2, such as CCTR.
  4. Select the first cell to be split, which in this example is cell D3.

The rest of the macro will use relative recording. This is because you are dealing with different numbers of rows of data every day.

  1. Open the Macros dropdown on the View tab and choose Use Relative References.
  2. From cell D3, press Ctrl+Shift+DownArrow. This will select all of the rows in column D (Figure 1).

Fig. 1

 

      3. On the Data ribbon tab, choose Text to Columns.
4. In the Text to Columns dialog, choose Delimited in step 1. Click Next.
5. In Step 2 of the Text to Columns dialog, choose Other as the delimiter.
6. Type a dash next to Other (Figure 2). You can skip Step 3 of the wizard, so click Finish.

 

 

Fig. 2

 

It is now time to stop the macro recorder. In the lower-left corner of the Excel screen, next to the word Ready, there is a square blue stop-recording icon. Click the square, as shown in Figure 3.

Fig. 3

 

 

Your macro has now been created and is ready to use.

 

The next time you receive the file from the IT department, you can open it, then press Alt+F8 to see a list of available macros. Choose SplitColumnD from the list and click Run (Figure 4).

Fig. 4

The macro will run, and the Cost Center/Account field will be split into two fields (Figure 5).

Fig. 5

Question 2: You Just Recorded a Macro to Run Some Procedure on One Row. Can You Now Select the Entire Column of Data and Have That Macro Run on Each Row in the Table?
Normally a macro that is written for one row is unlikely to work when applied to all rows. However, you can add three lines to your macro to expand it to work on each row in the table.

For example, suppose that you have just recorded a macro called MarkForCollection. You want to have that macro work on all of the selected cells. Follow these steps:

  1. Open the workbook that contains the recorded macro.
  2. Type Alt+F8 to display the Macros dialog.
  3. Choose the MarkForCollection macro and click the Edit button. You will now be in the VBA Editor.

Your macro will have different program code in it. However, it is going to start with the word Sub, it is going to have several green lines of comments, and it is going to end with the line End Sub. You are going to insert new lines in the macro at the places indicated by the arrows in Figure 6.

Fig. 6

Add a line after the green apostrophe line. Type the following:

For Each Cell in Selection

Cell.Select

Add a line before the End Sub. Type the following:

Next Cell

Your macro will now look like Figure 7.

Fig. 7

Press Alt+Q to close the editor and return to Excel.

You can now select several cells as shown in Figure 8.

Fig. 8

Run the macro, and the recorded macro will repeat for each cell in the selection (Figure 9).

Fig. 9

Bill Jelen is the author of 33 books about Microsoft Excel and the host of MrExcel.com. Submit questions to be considered for future articles by clicking on his byline at the top of this article and sending him an e-mail message, or by sending a note directly to BillJelen@cfo.com.

, , , ,

4 responses to “Spreadsheets: Using Text-to-Columns in a Macro”

  1. I wish this worked for me. I have recorded a macro as a step to convert US dates to UK dates using Text to Column, Delimited, no delimiters, format MDY. This works when I execute it manually, but when I use the macro, nothing happens.
    Sub Date_US_UK_3()
    ” Date_US_UK_3 Macro
    ‘ Macro recorded 06/01/2017 by mst

    Selection.TextToColumns Destination:=Range(“A1”), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True
    End Sub

  2. You need to change the default cell to be the active cell. Change Destination:=Range(“A1”)
    to
    Destination: ActiveCell

  3. Great info about text-to-column, thanks.
    Just before seeing this, I had created a macro to do exactly that but instead of Ctrl+Shift+DownArrow, I selected the whole column instead. It almost worked except the last row would always remained untouched. Any idea why …. below is that portion of the code:
    Selection.TextToColumns Destination:=Range(“A1”), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 4), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True

Leave a Reply

Your email address will not be published. Required fields are marked *