Never Use Autosum button while recording a macro - pg 30, chapter 1 of VBA and Macros, Microsoft Excel 2010

gmccray

New Member
Joined
Jan 9, 2009
Messages
16
Hi, I have been trying to get the code to work, but can't find the answer. It works up to the autofill part (copying the formula from cells e thru g. I have copied the code perfectly and I've even tried to modify it. Can you please show me what needs to be done?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Welcome to MrExcel,

When you say it isn't working, what happens when the code gets to the autofill part? Does it raise an error (if so, what is the message), or does it just not fill?

The example on pg 30 fills from A1:C1. It sounds like you are copying down in columns E:G. If you are modifying the macro from the example, please post your entire code.
 
Last edited:

gmccray

New Member
Joined
Jan 9, 2009
Messages
16
Hi Jerry,
Thanks for getting back to me and your assistance! It just stops after filling in cell e, it does not give an error, that line of code (autofill line is highlighted in yellow.
 

gmccray

New Member
Joined
Jan 9, 2009
Messages
16
Please post the entire code you are trying.
Code:
Sub FormatInvoice3()

' FormatInvoice2 Macro
' Third Try. Use Relative. Don't touch Autosum
'
' Keyboard Shortcut: Ctrl+Shift+K

ChDir _
"C:\Users\Redirection\gmccray\Documents\Personal\ExcelPractice\VBAAndMacros2010"
Workbooks.OpenText Filename:= _
"C:\Users\Redirection\gmccray\Documents\Personal\ExcelPractice\VBAAndMacros2010\invoice3.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True

' Turned on relative reference recording here
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").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:=Range("E:G"), Type:=xlFillDefault
ActiveCell.Range("A1:C1").Select

' Turned off relative recording here
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub
 
Last edited by a moderator:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Okay, now I understand your question. The autofill statement in the book reads:

Code:
 Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:=xlFillDefault
At this point in the execution of the code, the ActiveCell is E23. This expression is evaluated relative to E23:
Code:
ActiveCell.Range("A1:C1")
...and Excel interprets that as Range("E23:G23") relative to the entire worksheet.

If you replace the statement in the current code with this:
Code:
 Selection.AutoFill Destination:=Range("E23:G23"), Type:=xlFillDefault
...it would yield the same result as the book's example for this specific Invoice3.txt file. A problem would arise if you were to use a different text file that had 100 rows of data, since row 23 would be hard-coded.
 

gmccray

New Member
Joined
Jan 9, 2009
Messages
16
Ok, great! But what if you do not know what the last row will be?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Ok, great! But what if you do not know what the last row will be?
That's what the rest of the book explains! ;)

You have the right idea. The code should be written so that it will work for all the scenarios that you can reasonably expect. The book describes how you can start off learning VBA by using the macro recorder. The example shows that one way to find the last row is to use:

Code:
Selection.End(xlDown).Select
This isn't the best way, and in the next chapter the book will explain that you should avoid Selecting anything. However the book shows this example because it's one way to accomplish the objective with just the macro recorder.
 

Forum statistics

Threads
1,077,907
Messages
5,337,096
Members
399,125
Latest member
manibiotech

Some videos you may like

This Week's Hot Topics

Top