Constant Run-time error '1004'

Question

I am trying to sum up data in 3 columns one by one and paste the total in next sheet in three cells. I have come up with the following code. It works fine a couple of times but then starts throwing the error: Run-time error '1004' Application-defined or object-defined error.

Sub test1()

Dim Counter As Integer
Counter = 1

For i = 1 To 3

Do Until ThisWorkbook.Sheets("Sheet1").Cells(Counter, i).Value = ""

    ThisWorkbook.Sheets("Sheet1").Range(Cells(1, i), Cells(Counter, i)).Select
    Counter = Counter + 1

Loop

    Value1 = Application.WorksheetFunction.Sum(Selection)
    ThisWorkbook.Sheets("Sheet2").Cells(1, i).Value = Value1

Next i

End Sub

Have you tried any debugging with break points or On Error Goto to see on which line the error is occurring? — psubsee2003
Works fine for me - although I'm struggling to see what you are trying to achieve. As a tip you should include "Option Explicit" at the beginning of your code module, and declare the variables that you are using - e.g. in this case you declared counter but forgot Value1 and i. Not sure where the 1004 should come from, though. — ExternalUse

Daniel Daniel · Accepted Answer · 2012-09-14T16:18:38

You cannot select a range in a sheet that is not active, that is what is causing the error.

If you have any sheet besides Sheet1 active, your code will throw the error you listed. As a quick fix add Thisworkbook.Sheets("Sheet1").Activate before your loop.

As a better fix, you should try to get select out of your code.

Constant Run-time error '1004'

1 Answers