Edit an Excel Macro

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?