Excel displays Runtime Error 1004 when the macro you’re running cannot find the information you’re want to access. You may encounter this error as a result of incorrect referencing, or sometimes even when there is a problem in Excel configuration.
Sometimes, a simple restart can fix this issue. This is if your Excel program has a minor bug or a glitch. I highly suggest you check your code first before you dive into the other fixes.
What is Causing the Runtime Error 1004 in Excel?
As I said in the beginning, the Runtime Error 1004 is non-specific. This can make pinpointing the cause a bit challenging. However, in my experience, I have found that it mostly has to do with the referencing inside the code itself. You can go through the list I prepared and see what issue might seem the most relevant to you.
- Invalid Cell Reference
- Incorrect File Location
- Security Policy
- Corrupt Spreadsheet
- Outdated Excel Program
- Interference of Third-Party Applications
How to Fix Runtime Error 1004 in Excel?
We will approach this error by identifying and fixing any inconsistency in the code first. Then, we will go more into the configuration error that could potentially trigger the Runtime Error 1004 in Excel.
Change Referencing
Say, the macro you created ran well before. Now, however, you get the Runtime Error 1004 every time you wish to execute it. This could be because you renamed the sheet or named range the macro is referencing. Cross-check the macro and the referenced name to make sure this isn’t the issue.
If you have used relative referencing while referencing a range in your macro, the macro may be trying to extract values from empty cells. I suggest you use absolute referencing when you’re referencing a fixed range to make sure that the macro references only the correct cells.
Verify the File Location
If you’re commanding your macro to access a file from a folder, make sure you have entered the name of the folder correctly.
In most cases, if your macro works perfectly on your device but not your colleague’s, it’s because the file is in a different location on their device. Check for such inconsistency in your macro and run it again.
Enable Trust Access
When you disable access to the VBA project model, the macro may not access the information inside the referenced spreadsheet. Check if this setting is enabled from the Trust Center and try running the macro again once you enable it.
- Head to File from the menubar.
- Navigate to Options > Trust Center.
- Click on the Trust Center Settings button.
- From the sidebar, select Macro Settings.
- Under Developer Macro Settings, select the box next to Trust access to the VBA project object model.
- Click OK > OK.
Uninstall Microsoft Work
This fix is specific to Microsoft Excel version 2009 and below. Many users have reported encountering the runtime error 1004 when both Microsoft Works and Microsoft Office were installed on their devices. Uninstall Microsoft Work and try running the macro again to see if it resolves the issue.
- Open Task Manager (Ctrl + Shift + Esc) and End Task for all running applications.
- Use Windows Key + R on your keyboard to open the Run command.
- In the search bar, enter
appwiz.cpl
- Scroll down and locate Microsoft Work.
- Right-click on it and select Uninstall.
Delete the GWXL97.XLA File
The .xla files are used by Excel as an add-in to expand the functionality of the application. However, the GWXL97.XLA file can sometimes interfere with the Excel program and result in the runtime error 1004. Try deleting this file and running the macro again.
- Open File Explorer (Windows key + E)
- Navigate to
C:\Program Files\Microsoft Office
- Select your Office version > XLSTART.
- Right-click on GWXL97.XLA and choose Delete.
Open and Repair the Corrupted File
When a file is corrupted, it lacks the necessary information to make certain features work inside the program. You may also be experiencing the runtime error 1004 if your workbook is corrupted.
If you’re dealing with a corrupted spreadsheet, then you can use Excel’s built-in feature called Open and Repair to fix this issue.
- Head to File > Open.
- Select Browse under Other Locations.
- Navigate to the location your file is saved the select it.
- Select the fly-out for Open > Open and Repair.
- Choose Repair.