Error 1004 - VBA Begginer

[Felipeviv]

Hello,

I've just initiated in VBA and I'm following the book VBA and Macros for Excel 2007, but I keep getting the same error when I try to run some codes (even with the examples downloaded from the mrexcel website).

These are two examples (the errors appear in the bold parts):

Sub MacroFatura()
'
' MacroFatura Macro
' Fatura - Teste
'
' Atalho por teclado: Ctrl+b
'
ChDir "C:\Users\Carlos\Documents\Felipe\Coisas\VBA\VBA2007CH01"
Workbooks.OpenText Filename:= _
"C:\Users\Carlos\Documents\Felipe\Coisas\VBA\VBA2007CH01\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, 4), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
'Localiza a última linha com dados. Isso pode mudar todos os dias
FinalRow = Cells(Rows.Count, 1).End(x1Up).Row
TotalRow = FinalRow + 1
'Cria uma linha Total abaixo disso
Range(A & TotalRow).Value = "Total"
Range(E & TotalRow).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Rows("1:1").Font.Bold = True
Rows(TotalRow & ":" & TotalRow).Font.Bold = True
Cells.Columns.AutoFit
End Sub

And the other is

Sub Page26Macro()
'
' Second part of macro from Page 26. This was recorded with Relative
' References on during the first part of the macro
'
' Turned on relative recording here
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "'Total"
ActiveCell.Offset(0, 4).Range("A1").Select
' You will learn in chapter 2, the big problem is in the next row
Selection.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
' Turned off relative recording here
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
End Sub

The error that I get is Run Time error 1004 - Application-defined or object-defined error

So, I guess it's some configuration problem, but I have no idea where.

 

[Jonmo1]

Welcome to the board, and the world of VBA

This one..
FinalRow = Cells(Rows.Count, 1).End(x1Up).Row

That should be the letter L, not the number 1.

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row


This one

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

The first line simulates pressing CTRL + DOWN.
If all the cells below the currently active cell are blank when you run that line, it will go all the way to the last row of the sheet.
The next line tries to select the cell one row down from that.

If you are at the verry bottom of the sheet, and try to select a row below that, it doesn't exist, so it errors.

I'm guessing you're trying to add a formula in the cell one row below the last used row of the column right?

Try assigning the last used cell in the column to a variable, then use the offset on that variable.

Dim LastCell As Range
Set LastCell = Cells(Rows.Count, ActiveCell.Column).End(xlup)
LastCell.Offset(1,0).FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"

Hope that helps...

 

[Felipeviv]

Hey!

Thanks, that were the problems! It's fixed now, I have to start to pay attention to the writing and the logic of the whole thing.

Thanks a lot!