Macro Recording example in VBA AND MACROS: Chapter 1

[Joel Chan]

Hi,

I'm new to macro and I've just bought the book "VBA AND MACROS: Microsoft Excel 2010" and is trying out the example in Chapter 1, recording macro using relative references.

Btw, I'm still using excel 2003 which I felt so comfortable with.

I follow all the steps as mentioned on page 26 to page 29 with excel 2003. But the result differs to the one shown on the book (figure 1.16 on page 28). My macro recording works properly and the total sum adds up correctly. I was wondering where goes wrong... although it seems funny to pursuit to follow suit the book example to acquire a wrong result.

My macro script is posted as below. I observed that there is no comment "Turned on relative recording here ", even though I did "select on" the Relative Reference button as described in the book. I've also posted the result of the macro after the script.

Please help to clear my curiosity, whether am I on the right or wrong track.

Thank you.

Joel Chan


Sub ImportInvoiceRelative()
'
' ImportInvoiceRelative Macro
' Macro recorded 7/9/2012 by Joel Chan
'
'
Workbooks.OpenText Filename:="C:\Joel Chan\VBA Macro learning\invoice.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, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R[-17]C:R[-1]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub


[TABLE="width: 732"]
[TR]
[TD]InvoiceDate[/TD]
[TD]InvoiceNumber[/TD]
[TD]SalesRepNumber[/TD]
[TD]CustomerNumber[/TD]
[TD]ProductRevenue[/TD]
[TD]ServiceRevenue[/TD]
[TD]ProductCost[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123829[/TD]
[TD]s21[/TD]
[TD]c8754[/TD]
[TD="align: right"]538400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]299897[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123830[/TD]
[TD]s45[/TD]
[TD]c4056[/TD]
[TD="align: right"]588600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]307563[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123831[/TD]
[TD]s54[/TD]
[TD]c8323[/TD]
[TD="align: right"]882200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]52172[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123832[/TD]
[TD]s21[/TD]
[TD]c6026[/TD]
[TD="align: right"]830900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]494831[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123833[/TD]
[TD]s45[/TD]
[TD]c3025[/TD]
[TD="align: right"]673600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]374953[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123834[/TD]
[TD]s54[/TD]
[TD]c8663[/TD]
[TD="align: right"]966300[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]528575[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123835[/TD]
[TD]s21[/TD]
[TD]c1508[/TD]
[TD="align: right"]467100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]257942[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123836[/TD]
[TD]s45[/TD]
[TD]c3766[/TD]
[TD="align: right"]658500[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]308719[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2011[/TD]
[TD="align: right"]123837[/TD]
[TD]s54[/TD]
[TD]c4533[/TD]
[TD="align: right"]191700[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]109534[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123822[/TD]
[TD]s45[/TD]
[TD]c1007[/TD]
[TD="align: right"]271300[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123823[/TD]
[TD]s87[/TD]
[TD]c1878[/TD]
[TD="align: right"]338100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]165666[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123824[/TD]
[TD]s43[/TD]
[TD]c3068[/TD]
[TD="align: right"]567900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]265775[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123825[/TD]
[TD]s43[/TD]
[TD]c7571[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]55555[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123826[/TD]
[TD]s55[/TD]
[TD]c7181[/TD]
[TD="align: right"]37900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19811[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123827[/TD]
[TD]s43[/TD]
[TD]c7570[/TD]
[TD="align: right"]582700[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]292000[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123828[/TD]
[TD]s87[/TD]
[TD]c5302[/TD]
[TD="align: right"]495000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]241504[/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2011[/TD]
[TD="align: right"]123828[/TD]
[TD]s87[/TD]
[TD]c5302[/TD]
[TD="align: right"]495000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]241504[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8708656[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]4216001[/TD]
[/TR]
[/TABLE]

 

[MrExcel]

Are you using the 2003 edition of the book and Excel 2003?

The comment that appears in the book about turning on relative references is a comment that I manually typed into the macro so you could see where the recorder switched from absolute to relative reference. The macro recorder will not create comments beyond the first few lines with the description that you typed into the Record Macro dialog.

 

[Joel Chan]

I'm running 2003 Excel software and reading the "VBA AND MACROS: Microsoft Excel 2010" book. I don't seems to able to find the edition info from the book, but it stated "third printing: August 2011".

I got your point that the comment "Turn on relative recording here" is manually inserted. Then it would leave me no clue why my macro will turn out to be correct. Is it possible that only newer excel version than excel 2003 will generate the problem as described in the book?