Hello everyone!
I am new to VBA. I am reading Mr Excel's book "VBA and Macros for Microsoft Office Excel 2007".
I am trying to re-create from scratch and run the code from Chapter 2, pp. 59-60 of the book (see below). I do it using F8. However, as soon as I get to the line that defines a new variable (FinalRow = Cells(Rows.Count, 1).End(xlUp).Row), I get an error:
"Compile error: Variable not defined"
My code is correct (in fact, I copied it from the file I downloaded from the link that was provided in the book)
Advice?
Thank you!
Dimitri
Sub ImportInvoiceFixed()
'
' ImportInvoice Macro
' Written 10/23/2003 by Bill Jelen This macro will import invoice.txt and add totals.
'
' Keyboard Shortcut: Ctrl+i
'
' Workbooks.OpenText Filename:= _
' "C:\invoice.txt", Origin _
' :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
' , ConsecutiveDelimiter:=False, Tab:=True, 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
' Find the last row with data. This might change every day
' FinalRow = Range("A65536").End(xlUp).Row - also does not work
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row)
TotalRow = FinalRow + 1
' Build a Total row below this
Range("A" & TotalRow).Value = "Total"
Range("E" & TotalRow).Formula = "=SUM(E2:E" & FinalRow & ")"
Range("E" & TotalRow).Copy Destination:=Range("F" & TotalRow & ":G" & TotalRow)
Rows("1:1").Font.Bold = True
Rows(TotalRow & ":" & TotalRow).Font.Bold = True
Cells.Columns.AutoFit
End Sub
I am new to VBA. I am reading Mr Excel's book "VBA and Macros for Microsoft Office Excel 2007".
I am trying to re-create from scratch and run the code from Chapter 2, pp. 59-60 of the book (see below). I do it using F8. However, as soon as I get to the line that defines a new variable (FinalRow = Cells(Rows.Count, 1).End(xlUp).Row), I get an error:
"Compile error: Variable not defined"
My code is correct (in fact, I copied it from the file I downloaded from the link that was provided in the book)
Advice?
Thank you!
Dimitri
Sub ImportInvoiceFixed()
'
' ImportInvoice Macro
' Written 10/23/2003 by Bill Jelen This macro will import invoice.txt and add totals.
'
' Keyboard Shortcut: Ctrl+i
'
' Workbooks.OpenText Filename:= _
' "C:\invoice.txt", Origin _
' :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
' , ConsecutiveDelimiter:=False, Tab:=True, 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
' Find the last row with data. This might change every day
' FinalRow = Range("A65536").End(xlUp).Row - also does not work
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row)
TotalRow = FinalRow + 1
' Build a Total row below this
Range("A" & TotalRow).Value = "Total"
Range("E" & TotalRow).Formula = "=SUM(E2:E" & FinalRow & ")"
Range("E" & TotalRow).Copy Destination:=Range("F" & TotalRow & ":G" & TotalRow)
Rows("1:1").Font.Bold = True
Rows(TotalRow & ":" & TotalRow).Font.Bold = True
Cells.Columns.AutoFit
End Sub