Excel 2010 Macro and AutoSum
It is important to recognize that macro recorder will never correctly record the intent of the AutoSum button.
If you are in cell E99 and click the AutoSum button, Excel starts scanning from cell E98 upward until it locates a text cell, a blank cell, or a formula. Then, it suggests a formula that sums everything between the current cell and the found cell. However, the macro recorder records the particular result of that search on the day that the macro was recorded. Rather than record something like “do the normal AutoSum logic,” the macro recorder inserts a single line of code to add up the previous 98 cells. The quick fix is to type a SUM function that uses a mix of relative and absolute row references. If you type =SUM(E$2:E10) while the macro recorder is running, Excel correctly adds code that will always sum from a fixed row two down to the relative reference that is just above the current cell.
Here is the resulting code:
Sub FormatInvoice3()
‘
‘ FormatInvoice2 Macro
‘ Third try. Use relative. Don’t touch AutoSum
‘
‘ Keyboard Shortcut: Ctrl+Shift+K
‘
Workbooks.OpenText Filename:=”C:\Users\Owner\Documents\invoice.txt”, Ori-
gin _
:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:= _
True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), Ar-
ray(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)),
TrailingMinusNumbers _
:=True
‘ Relative turned on here
Selection.End(xlDown).Select
ActiveCell.FormulaR1C1 = “Total”
ActiveCell.Offset(0, 4).Range(“A1”).Select
‘ Don’t use AutoSum. Type this formula:
Selection.FormulaR1C1 = “=SUM(R2C:R[-1]C)”
Selection.AutoFill Destination:=ActiveCell.Range(“A1:C1”), Type:= _
xlFillDefault
ActiveCell.Range(“A1:C1”).Select
‘ Relative turned off here
ActiveCell.Rows(“1:1”).EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range(“A1”).Select
End Sub