Microsoft KB Archive/88144

{| = Recorded CROSSTAB Macro Is Interactive if Fields Not Selected =
 * width="100%"|

Last reviewed: November 29, 1994

Article ID: Q88144 The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0

SUMMARY
In Microsoft Excel for Windows, it is possible to record a macro that automatically creates a cross-tabulation table. However, when you create your cross-tabulation table, you must select fields for both row categories AND for column categories, or the macro runs the Crosstab ReportWizard instead of building a cross-tabulation table automatically.

For information about how to create a cross-tabulation table, see pages 346-356 of the &quot;User's Guide 1&quot; for version 4.0.

Workaround
To correct this problem, record your macro again, this time selecting fields both for row categories and for column categories. After recording your macro, edit the CROSSTAB.CREATE function in your macro to remove the argument values for the category you don't want included.

To remove unwanted category argument values:

  Activate your macro sheet. If you are using the Global macro sheet, choose Unhide from the Window menu and select GLOBAL.XLM from the list in the Unhide dialog box.  Select the cell that contains the CROSSTAB.CREATE function in the macro you recorded. Delete the argument for the category you want to remove.

Each category argument consists of an array of information surrounded by braces. Delete the argument (including the braces) but don't remove the comma. The comma must be left as a placeholder for the argument you remove. For example, in the following formula

=CROSSTAB.CREATE({&quot;Vendor&quot;,0,&quot;Auto&quot;,&quot;Auto&quot;,&quot;NNNNNNN&quot;},  {&quot;Region&quot;,0,&quot;Auto&quot;,&quot;Auto&quot;,&quot;NNNNNNN&quot;},,TRUE,TRUE,1,TRUE,TRUE) removing the column category argument (field name = Region) results in  =CROSSTAB.CREATE({&quot;Name&quot;,0,&quot;Auto&quot;,&quot;Auto&quot;,&quot;NNNNNNN&quot;},,,TRUE,   TRUE,1,TRUE,TRUE) Notice that the comma in the above formula has been left as a place holder. (The formulas above have been word wrapped for readability. They should appear on one line without any spaces.) When you record a macro that creates a cross-tabulation table, Microsoft Excel enters the CROSSTAB.CREATE function, including its arguments, in a cell on your macro sheet.

If you don't select field names for both row categories and column categories when creating your cross-tabulation table, the Microsoft Excel Macro Recorder uses the interactive form of the function

(=RUN&quot;CROSSTAB.CREATE?&quot;, FALSE)) instead of the non-interactive form:  (=CROSSTAB.CREATE.)