PDA

View Full Version : Solved: VBA Assigning Variable / Runtime Error 1004



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

CharlesH
01-25-2011, 10:53 AM
Hi,

Welcome to the forum.

Seeing that you opened a file If you know the sheet name of the file then I think you would need to reference the sheet name in the code.



FinalRow = Sheets("YourSheetName").Range("A65536").End(XlUp).Row

CreganTur
01-25-2011, 11:02 AM
You're getting an error on that line because it's XLUp, not X1Up.

Legendmir
01-25-2011, 11:13 AM
Hi Guys

Thanks for the quick responses

when i try to add in Sheets ("invoice.txt") I receive a "compile error: expected: end of statement" error message

AHA! Thankyou CreganTur - it was indeed an L ..... it looks exactly like a 1 in the book... how odd


Many thanks to you both!

mdmackillop
01-25-2011, 01:07 PM
If you type in lower case, the mixed upper/lower will appear if the syntax is correct. All lowercase function words usually means a typo.