Subscribe Us

Enter your email address:



Labels: , ,

Task One: Opening the Report File

The orders for the most recent month, November 2007, are in the Report text file. The first task is to open the file, splitting it into columns as you do, and move the file into the workbook with the macro. 


Introduction to Macro Record in Excel 2007


Creating a Simple Macro

Excel has a large collection of tools conveniently available on the Ribbon or on shortcut menus. Sometimes, however, a built-in tool doesn’t work quite the way you want. Creating a macro to enhance a built-in tool is a good way to get started with writing macros. 


Using Array Formulas


Here’s a simple example that illustrates how array formulas work. In the Expenses workbook shown, the 2008 BUDGET totals are calculated using a separate formula for each month, as shown here:
January 2008 BUDGET =C11*$C$3
February 2008 BUDGET =D11*$C$3
March 2008 BUDGET =E11*$C$3

You can replace all three formulas with a single array formula by following these steps:
  1. Select the range that you want to use for the array formula. In the 2008 BUDGET example, you’d select C13:E13.
  2. Type the formula and, in the places where you would normally enter a cell reference, type a range reference that includes the cells you want to use. Don’t—I repeat, don’t— press Enter when you’re done. In the example, you’d enter =C11:E11*$C$3.
  3. To enter the formula as an array, press Ctrl+Shift+Enter. The 2008 BUDGET cells (C13, D13, and E13) now all contain the same formula: {=C11:E11*$C$3}