VBA Runtime Error 1004 “Application-defined or Object-defined error”

The VBA Runtime Error 1004 can be caused by many things. In this article, I’ll show a few different situations and how to deal with them.

VBA code refers to a range that doesn’t exist

This code usually happens when VBA code refers to a cell or range that doesn’t exist. For example, this code is correct.

But when you try to access the following ranges, the program will return an error in both cases.

The first line returned a 1004 error because Excel contains only 16,384 columns and the last column is XFD. We don’t have the “myRange” range in the second case. In this case, the application also returns the error. After you create the range, the error won’t show up.

You should really pay attention when you are using loops, especially for columns. You can go out of the index without noticing.

You are trying to enter a value that is not a range

The following code will result in an error because the program cannot recognize values inside the Range property as a range.

Select a worksheet explicitly

Sometimes when you choose a worksheet implicitly, you may have a 1004 error.

Take a look at this code. Here, you selected the active sheet, but when you activate a different sheet this may result in an error.

Instead, use the code when you explicitly select the sheet name.

You are using the protected mode

You may also get the 1004 error if your worksheet is in protected mode. Disabling the protection might get rid of this error.

Macro recorded at the worksheet level

Sometimes you may get this error if you record a macro at the worksheet level. You should create a module and enter the code there. In order to do so, go to VBE (Alt + F11) and right-click on the module’s icon. Insert a new module and enter your code there.

These situations are the most common for error 1004 to occur. I hope it fixed your problem.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba