Error 1004 - VBA Begginer


New Member
Mar 29, 2009

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)), _
'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
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
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:= _
' Turned off relative recording here
Selection.Font.Bold = True
Selection.Font.Bold = True
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.

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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

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...
Upvote 0

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!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back