Legendmir
01-25-2011, 10:41 AM
Hi
Sorry if I am talking absolute rubbish - I am a VBA Noob
I have a text book which has told me to write the following code to format a textfile:
Sub importinvoicecorrected()
'
' importinvoicecorrect Macro
' Macro recorded 25/01/2011 by XXXXXXX
'
' Keyboard Shortcut: Ctrl+k
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\XXXXXX\Desktop\invoice.txt", Origin:=xlMSDOS, _
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), Array(8, 1)), _
TrailingMinusNumbers:=True
' Find the last row of with data. This might change every day
FinalRow = Range("A65536").End(x1Up).Row
TotalRow = FinalRow + 1
' Build a total row before this
Range("A" & TotalRow).Value = "Total"
Range("E" & TotalRow).Resize(1, 3).FormulaR1C1 = "=SUM (R2C:R[-]C) "
Rows("1:1").Font.Bold = True
Rows(TotalRow & ":" & TotalRow).Font.Bold = True
Cells.Columns.AutoFit
End Sub
However when the macro gets to the :
FinalRow = Range("A65536").End(x1Up).Row
I get the runtime 1004 error.. I am sure I am typing it exactly as it is in the book.. please help!!
many thanks
Sorry if I am talking absolute rubbish - I am a VBA Noob
I have a text book which has told me to write the following code to format a textfile:
Sub importinvoicecorrected()
'
' importinvoicecorrect Macro
' Macro recorded 25/01/2011 by XXXXXXX
'
' Keyboard Shortcut: Ctrl+k
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\XXXXXX\Desktop\invoice.txt", Origin:=xlMSDOS, _
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), Array(8, 1)), _
TrailingMinusNumbers:=True
' Find the last row of with data. This might change every day
FinalRow = Range("A65536").End(x1Up).Row
TotalRow = FinalRow + 1
' Build a total row before this
Range("A" & TotalRow).Value = "Total"
Range("E" & TotalRow).Resize(1, 3).FormulaR1C1 = "=SUM (R2C:R[-]C) "
Rows("1:1").Font.Bold = True
Rows(TotalRow & ":" & TotalRow).Font.Bold = True
Cells.Columns.AutoFit
End Sub
However when the macro gets to the :
FinalRow = Range("A65536").End(x1Up).Row
I get the runtime 1004 error.. I am sure I am typing it exactly as it is in the book.. please help!!
many thanks