Task Two: Filling In Missing Labels


When the order-processing system produces a summary report, it enters a label in a column only the first time that label appears. Leaving out duplicate labels is one way to make a report easier for a human being to read, but for the computer to sort and summarize the data properly, you need to fill in the missing labels.

image from book

You might assume that you need to write a complex macro to examine each cell and determine whether it’s empty, and if so, what value it needs. In fact, you can use Excel’s built-in capabilities to do most of the work for you. Because this part of the project introduces some powerful worksheet features, start by going through the steps before recording the macro.

Select Only the Blank Cells

Look at the places where you want to fill in missing labels. What value do you want in each empty cell? You want each empty cell to contain the value from the first nonempty cell above it. In fact, if you were to select each empty cell in turn and put into it a formula pointing at the cell immediately above it, you would have the result you want. The range of empty cells is an irregular shape, however, which makes the prospect of filling all the cells with a formula daunting. Fortunately, Excel has a built-in tool for selecting an irregular range of blank cells.

  1. In a copy of the Nov2007 worksheet in the Chapter02 workbook, select cell A1.

  2. On the Home tab of the Ribbon, in the Editing group, click the Find & Select arrow, and then click Go To Special.

  3. In the Go To Special dialog box, click Current region, and then click OK.

    image from book

    Excel selects the current region-the rectangle of cells including the active cell that is surrounded by blank cells or worksheet borders.

    Tip 

    You also can press Ctrl+* to select the current region. Press and hold the Ctrl key while pressing either * on the numeric keypad or Shift+8 on the regular keyboard.

  4. Once again, click the Find & Select arrow on the Ribbon, and then click Go To Special.

  5. In the Go To Special dialog box, click the Blanks option, and then click OK.

    Excel subselects only the blank cells from the selection. These are the cells that need new values.

    image from book

    Excel’s built-in Go To Special feature can save you-and your macro-a lot of work.

Fill the Selection with Values

You now want to fill each of the selected cells with a formula that points to the cell above. Normally when you enter a formula, Excel puts the formula into only the active cell. You can, however, if you ask politely, have Excel put a formula into all the selected cells at once.

  1. With the blank cells selected and D3 as the active cell, type an equal sign ( = ), and then press the Up Arrow key to point to cell D2.

    The cell reference D2-when used in a formula in cell D3-actually means “one cell above me in the same column.”

  2. Press Ctrl+Enter to fill the formula into all the currently selected cells.

    When more than one cell is selected, if you type a formula and press Ctrl+Enter, the formula is copied into all the cells of the selection. (If you press the Enter key without pressing and holding the Ctrl key, the formula goes into only the one active cell.) Each cell with the new formula points to the cell above it.

    image from book

  3. Press Ctrl+* to select the current region.

  4. Right-click any selected cell, and click Copy. Right-click any selected cell, click Paste Special, click the Values option, and then click OK.

  5. Press the Esc key to get out of copy mode, and then select cell A1.

Now the block of cells contains all the missing-label cells as values, so the contents won’t change if you happen to re-sort the summary data.

Record Filling In the Missing Values

In this section, you’ll select a different copy of the imported worksheet and follow the same steps, but with the macro recorder turned on.

  1. Select a copy of the Nov2007 worksheet (one that doesn’t have the labels filled in), or run the ImportFile macro again.

  2. Click the Record Macro button, type FillLabels as the name of the macro, and then click OK.

  3. Select cell A1 (even if it’s already selected), and press Ctrl+* to select the current region.

  4. Click the Find & Select arrow on the Ribbon, click Go To Special, click the Blanks option, and then click OK.

  5. Type an equal sign ( = ), press the Up Arrow key, and press Ctrl+Enter.

  6. Press Ctrl+*, right-click, and click Copy. Then right-click, click Paste Special, click the Values option, and then click OK.

  7. Press the Esc key to get out of copy mode, and then select cell A1.

  8. Click the Stop Recording button, and then save the Chapter02 workbook.

    You’ve finished creating the FillLabels macro.

Watch the FillLabels Macro Run

Now read the macro while you step through it.

  1. Select (or create) another copy of the imported worksheet.

  2. On the View tab of the Ribbon, click the View Macros button, select the FillLabels macro, and then click Step Into.

    The Visual Basic editor window appears, with the header statement of the macro highlighted.

  3. Press F8 to move to the first statement in the body of the macro:

    Range("A1").Select 

    This statement selects cell A1. It doesn’t matter how you got to cell A1-whether you clicked the cell, pressed Ctrl+Home, or pressed various arrow keys-because the macro recorder always records just the result of the selection process.

  4. Press F8 to select cell A1 and highlight the next statement:

    Selection.CurrentRegion.Select 

    This statement selects the current region of the original selection.

  5. Press F8 to select the current region and move to the next statement:

    Selection.SpecialCells(xlCellTypeBlanks).Select 

    This statement selects the blank special cells of the original selection. (The word SpecialCells is a method that handles many of the options in the Go To Special dialog box.)

  6. Press F8 to select just the blank cells and move to the next statement:

    Selection.FormulaR1C1 = "=R[-1]C" 

    This statement assigns =R[-1]C as the formula for the entire selection. When you entered the formula, the formula you saw was =C2, not =R[-1]C. The formula =C2 really means “get the value from the cell just above me,” but only if the active cell happens to be cell C3. The formula =R[-1]C also means “get the value from the cell just above me,” but without regard for which cell is active.

    You could change this statement to Selection.Formula = "=C2" and the macro would work exactly the same-provided that the order file you use when you run the macro is identical to the order file you used when you recorded the macro and that the active cell happens to be cell C3 when the macro runs. However, if the command that selects blanks produces a different active cell, the revised macro will fail. The macro recorder uses R1C1 notation so that your macro will always work correctly.

    See Also 

    For more information about R1C1 notation, see the section titled “R1C1 Reference Style” in Chapter 4, “Explore Range Objects.”

  7. Press F5 to execute the remaining statements in the macro:

    Selection.CurrentRegion.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _   Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select 

    These statements select the current region, convert the formulas to values, cancel copy mode, and select cell A1.

    See Also 

    The final statements in this macro are identical to the PasteSpecial macro from the section titled “Convert a Formula to a Value by Using a Macro” in Chapter 1, “Make a Macro Do Simple Tasks.”

You’ve completed the macro for the second task of your month-end project. Now you can start a new macro to carry out the next task-adding dates.



Microsoft Office Excel 2007 Visual Basic for Applications Step by Step
Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))
ISBN: 0735613591
EAN: 2147483647
Year: 2004
Pages: 99
Authors: Reed Jacobsen

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net