Run-time error 1004 is Excel’s general refusal: VBA asked Excel to do something with a range, a sheet or a workbook that Excel cannot do at that moment, and the message after the number says which. The usual causes are a Range or Cells call that points at the wrong sheet, selecting on a sheet that is not active, a protected sheet, a file path that does not exist, a formula string Excel cannot read, and a row or column number of zero. Click Debug, read the full message, and match it below.
Match the message
| The message after 1004 | Cause |
|---|---|
| Method ‘Range’ of object ‘_Worksheet’ failed | 1. Cells inside Range points at another sheet |
| Select method of Range class failed | 2. Selecting on a sheet that is not active |
| The cell or chart you’re trying to change is on a protected sheet | 3. A protected sheet |
| Sorry, we couldn’t find … Is it possible it was moved, renamed or deleted? | 4. A wrong path in Workbooks.Open |
| Application-defined or object-defined error | 5. A formula string Excel cannot read, or 6. a row or column of 0 |
1. Cells that point at another sheet
The most common 1004 of all. In the first line below, Range belongs to ws, but the two Cells inside it belong to whichever sheet is active. When that is a different sheet, Excel refuses.
ws.Range(Cells(2, 1), Cells(lastRow, 4)).ClearContents ' fails when ws is not active
With ws
.Range(.Cells(2, 1), .Cells(lastRow, 4)).ClearContents ' every part belongs to ws
End With
The dots in front of .Range and .Cells tie each one to ws.
2. Selecting on a sheet that is not active
ws.Range("A1").Select fails unless ws is the active sheet. The real fix is not to select at all: work with the range directly, which is also faster. See Why .Select and .Activate Make Your VBA Slow.
3. A protected sheet
Writing to a locked cell on a protected sheet raises 1004. Unprotect, write, protect again:
ws.Unprotect Password:="secret"
ws.Range("B2").Value = Date
ws.Protect Password:="secret"
Or let code write while users cannot
Protect the sheet in Workbook_Open with UserInterfaceOnly:=True. Excel does not keep that setting after the file is closed, so it has to run at every open.
4. A path that does not exist
Workbooks.Open on a file that is not there stops with 1004. Check first:
If Dir(filePath) = "" Then
MsgBox "Cannot find " & filePath
Exit Sub
End If
Workbooks.Open filePath
5. A formula string Excel cannot read
.Formula expects the English function names and commas, whatever your regional settings; .FormulaLocal takes your own. Quotes inside the formula are doubled. .FormulaR1C1 writes one formula to a whole column with relative references:
ws.Range("C2:C100").Formula = "=IF(A2="""",""Missing"",A2)"
ws.Range("C2:C100").FormulaR1C1 = "=IF(RC[-2]="""",""Missing"",RC[-2])"
6. A row or column of zero
Cells(i - 1, 1) when i is 1 asks for row 0, which does not exist, and Excel answers with 1004. The same happens with Range("A" & n) when n is 0. Check the counter where it is calculated, not where it fails.
Common questions
Why does my macro work from the editor but fail from a button?
The active sheet is different. From the editor, the sheet you were looking at is active; from a button on another sheet, that sheet is. Any Range or Cells without a sheet in front of it follows the active sheet, so put the sheet in front of every one.
Is error 1004 the same as error 9?
No. Error 9, Subscript out of range, means a sheet or workbook name that does not exist. Error 1004 means the object exists, but Excel refused what was asked of it.
Can I just add On Error Resume Next?
It hides the error, and the macro carries on with that step not done, so the data ends up half-processed. Fix the cause. Use error handling to report a problem clearly, not to skip it.
When the Fix Is Bigger Than One Line
The same 1004 that keeps coming back usually means the macro depends on what happens to be on screen:
- Selection-driven code that only works with the right sheet active
- Recorded macros with every click recorded
- No checks before opening files or writing to protected sheets
A rewrite keeps what the macro does and removes what makes it fragile.
Macro Still Broken?
Send us the error message word for word and the code, and we’ll fix it.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.