Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion


Edit an Excel Macro

By mcarter ·
I have created an Excel macro which is used to format data imported from a text file. The macro also contains formulas that are copied down. When I try to run the macro on a range that contains more or less records than the original file used to create the macro, I am getting errors. When I run the macro, how do I tell Excel to copy the formulas down to the last cell, whether or not it is more or less records than the original file?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Autoformat on?

by seanferd In reply to Edit an Excel Macro

I'll let Excel help explain it for me:
Extend formats and formulas to additional rows
By default, Microsoft Excel automatically formats new data that you type at the end of a range to match the preceding rows. Excel also automatically copies formulas that have been repeated in the preceding rows and extends them to additional rows.

Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.

You can turn this option off (or back on again) at any time:

On the Tools menu, click Options.
On the Edit tab, clear the Extend data range formats and formulas check box to turn automatic formatting off.
To turn automatic formatting back on again, select the Extend data range formats and formulas check box.
Is that what you're looking for? Or something more like this?
Copy formulas when an external data range expands

Enter a formula in a cell adjacent to the first row of data in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.).
Note The first row of data might be the first or second row in the external data range, depending on whether the first row contains headers.

Select the cell and double-click the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) to copy the formula to all rows in the external data range.
Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.
Note If the external data range expands when you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.), Microsoft Excel copies only those formulas that are immediately adjacent to or within the external data range
As to coding this action into the macro, I have no idea.

Collapse -

Repost as a Question.

by CharlieSpencer In reply to Edit an Excel Macro

Please repost this using the 'Ask a Question' button. While both forums are open to all members, many who specialize in answering questions don't visit the 'Discussion' forum as much as the 'Questions'.

Related Discussions

Related Forums