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

[gmccray]

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]

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.

 

[gmccray]

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.

 

[Jerry Sullivan]

Please post the entire code you are trying.

 

[gmccray]

Please post the entire code you are trying.

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

 

[Jerry Sullivan]

Okay, now I understand your question. The autofill statement in the book reads:

 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:

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:

 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]

Ok, great! But what if you do not know what the last row will be?

 

[Jerry Sullivan]

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:

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.

 

[gmccray]

Ok, thanks much!