Ошибка vba method range of object global failed

I have a problem with excel, with a form that generates a reference no. But when I try to generate the reference no. it has an error message saying : Run-time error '1004' : Method 'Range' of o...

I have a problem with excel, with a form that generates a reference no. But when I try to generate the reference no. it has an error message saying :

Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed

When I click on Debug button , it shows the code as below:

It highlight the error on 4th line of the code

Sub clearTemplate()
    ' Clear Template Content
    Range(inputTemplateHeader) = NO_ENTRY
    Range(inputTemplateContent) = NO_ENTRY     - (highlighted error)
End Sub

Sub clearRefNo()
    ' Clear cell G2 reference number
    Range(cellRefNo) = NO_ENTRY

    ' Open "Report_ref_no.xls"
    If Not (IsFileOpen) Then Workbooks.Open filename:=ThisWorkbook.Path & "" & FACCESS

    ' Activate "Report_ref_no.xls"
    Windows(FACCESS).Activate

    ' Access column D
    Range(cellFirstRefNo).Select
    Selection.End(xlDown).Select

    If refNo = Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
        ' Log Development Code column
        Cells(ActiveCell.Row, ActiveCell.Column) = NO_ENTRY

        ' Log Issuer column
        Cells(ActiveCell.Row, ActiveCell.Column + 1).Value = NO_ENTRY

        ' Log Date column
        Cells(ActiveCell.Row, ActiveCell.Column + 2).Value = NO_ENTRY
    End If

    ' Save & Close workbook
    ActiveWindow.Close True
End Sub

Can anyone help me with this problem ? I don’t know what has gone wrong?

Community's user avatar

asked Aug 29, 2012 at 9:26

Melissa Charn's user avatar

2

When you reference Range like that it’s called an unqualified reference because you don’t specifically say which sheet the range is on. Unqualified references are handled by the «_Global» object that determines which object you’re referring to and that depends on where your code is.

If you’re in a standard module, unqualified Range will refer to Activesheet. If you’re in a sheet’s class module, unqualified Range will refer to that sheet.

inputTemplateContent is a variable that contains a reference to a range, probably a named range. If you look at the RefersTo property of that named range, it likely points to a sheet other than the Activesheet at the time the code executes.

The best way to fix this is to avoid unqualified Range references by specifying the sheet. Like

With ThisWorkbook.Worksheets("Template")
    .Range(inputTemplateHeader).Value = NO_ENTRY
    .Range(inputTemplateContent).Value = NO_ENTRY
End With

Adjust the workbook and worksheet references to fit your particular situation.

answered Aug 29, 2012 at 12:54

Dick Kusleika's user avatar

Dick KusleikaDick Kusleika

32.4k4 gold badges51 silver badges72 bronze badges

This is one of the most common run-time errors. By the end of this article, you will understand why that is and how to fix it. A run-time error is the type of error that occurs during the execution of the code. VBA does not know about it prior to actually running the code. There are different variations of this error; we will provide an example of each of the types below.

EXAMPLE 1: VBA Runtime Error 1004: Method ‘Range’ of object ‘_ Global’ failed

When a range reference is not correct. It could be incorrect because it’s misspelled. It could also be incorrect because it’s trying to get a range that is at an impossible value, such as row 0 or row -2. In the example below, we’re referring to row zero and we’re using the wrong syntax. VBA recognizes that we’re trying to refer to a range but it sees that the arguments (parameters) written within the range in order to identify it are written incorrectly; there should be no commas when using Range().

Sub Range_Error()
Range(0, 1).Select
End Sub

range of object global failed

Instead, we should write the code as follows:

Sub Range_Error()
Range(“A1”).Select
End Sub

We could also run into this problem unintentionally if we are looping a variable, such as X, and it becomes zero.

In the below example, X is equal to 5 and the loop is set to keep reducing X by 1 until it reaches -1 then it would not execute. But an error will occur when X = 0.

I set the code to write ‘Correct’ in each of the cells up until the cell we are not able to insert values into. Our code will insert ‘Correct’ in Range(“A5) to Range(“A1”) then will give the error when we ask it to insert ‘Correct’ into Range(“A0”).

Note that we didn’t define X to be a String, so VBA assumes it’s a variant. This means it can be any type of data, so Excel assumes that it’s the type of data that should be utilized in the context we’re utilizing it in. In this case, X would be considered a string. But when X is zero, Excel doesn’t know that it should be string, since “A0” doesn’t exist as a range. So it treats the zero as a number, which triggers this error, because it’s syntax related, meaning what’s written inside Range() is not written correctly from a syntax point of view.

Sub Range_Error()
X = 5
Do Until X = -1
Range("A" & X) = "Correct"
X = X - 1
Loop
End Sub

method range of object global failed

To correct the problem, we need to stop our code from running the loop once X reaches 0.

Sub Range_Error()
X = 5
Do Until X = 0
Range("A" & X) = "Correct"
X = X - 1
Loop
End Sub

EXAMPLE 2: VBA Run Time Error 1004: That Name is already taken. Try a different One

This error is shown when we are trying to give the same name to a worksheet which has already been given to another worksheet in the same workbook. Given that we already have a sheet named “Sheet1”, we create a new sheet, we click on it so that it becomes the active sheet, and we try to name it “Sheet1” by running the below code. We get an error immediately once we reach the sheet name changing line.

Sub Error_Name_Taken()
ThisWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Name = "Sheet1"
End Sub

name already taken

EXAMPLE 3: VBA Run Time Error 1004: Select Method of Range class failed

This error occurs when we attempt to activate a range through VBA in another worksheet (not the currently active one) without activating that worksheet first.

Sub Error_Select_Failed()
ThisWorkbook.Sheets("Sheet2").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
End Sub

select method of range class failed

To correct the issue, we would need to first activate “Sheet1” first before attempting to select a range inside of it. Adding a line to active the worksheet resolves the issue.

Sub Error_Select_Failed()
ThisWorkbook.Sheets("Sheet2").Activate
ThisWorkbook.Sheets("Sheet1").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
End Sub

EXAMPLE 4: VBA Runtime Error 1004: Method ‘Open’ of object ‘Workbooks’ failed

This error occurs when we use VBA to open a workbook that cannot be opened. The reason that Excel is unable to open the workbook could be because it is already open, or the workbook is being used by another program at that moment. A special case exists when you attempt to open a file in read-only mode while the file is corrupt. This would give an error because Excel can open a corrupt file only if it is allowed to repair it first, which it does by writing to it. So, if you open a corrupt file in read-only mode, you are essentially preventing it from repairing the file and hence it would fail to open.

EXAMPLE 5: VBA Runtime Error 1004: file format is not valid

This error, like the one in example 5, is related to attempting to open a file. In this situation, however, we are trying to open a file that isn’t an Excel file; the file doesn’t have any of the Excel extensions ( .xlsx, .xls, .xlsb, .xlsm, etc.)

Sub error_workbook_open()
Workbooks.Open "C:UsersmmostDropboxDaniel TrohaRun-time error 1004.docx"
End Sub

file format is not valid

The code is attempting to open a word document file with extension .docx. Using ‘Workbooks.Open’ can only be used for files that have Excel extensions. To open documents of different extensions, we will need to take a completely different approach. For example, the below code opens a word document.

Sub error_word_doc_open()
Dim wordapp
Dim strFileName As String
strFileName = "C:UsersmmostDropbox Else Without If.docx"
Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open strFile
wordapp.Visible = True
End Sub

EXAMPLE 6: VBA Runtime Error 1004: Sorry We Couldn’t Find:

This error occurs whenever we are trying to open a file that does not exist at the specified path. When faced with this error, it’s important to check three aspects of the file path; file location, file name, and file extension. Any of these parameters could be wrong, so check each of them carefully.

Sub error_workbook_open()
Workbooks.Open "C:UsersmmostDropboxAnother Workbook.xls"
End Sub

couldn't find workbook

EXAMPLE 7: VBA Runtime Error 1004: Application-defined or Object-defined error

This error encompasses a wide range of possibilities. The error is triggered due to violating one of the rules that are used to handle the object you’re working with.

For example, in the following code, I try to select a cell that exists on row -1 and column 1. There is no such cell as the minimum row number allowed is 1, thus, VBA determines that I violated this rule and throws this error.

Note that this is different from Example 1 in that the syntax I am using here is perfectly correct, but I am violating particular rules that once violated, trigger an error. The wide range of these rules make this one of the most common errors in VBA. To resolve it, you will want to know more about the object you’re working with (in this case it’s a cell in the worksheet) and understand the rules and limitations that exist on the various values that you can use with the object.

Sub error_object_defined()
ThisWorkbook.Sheets(1).Cells(-1, 1).Select
End Sub

application defined or object defined error

To wrap it up, here’s one example of where a syntax error calling a function causes this error to occur.

Hello everyone, I have this error in my code (red part). I guess it´s a just a little mistake, but I can´t find it. Maybe someone can help me.

It is made on CommandButton for sort by two keys and I remake it from my professor’s code (and change name of lists ect.) and insert some condions, but it doesn’t work.

Where cmbx_klic1 is ComboBox for first key and cmbx_klic2 is ComboBox for second key for names by what you can sort it. And optb_vzest_1 is OptionButton for ascending for first key and optb_vzest_2 for ascending for second key. The last is check_razeni — CheckBox for not haveing one key for sorting, but two keys. And the list have really the same name as the part of the list.

Here the Code so far:

Private Sub cmdb_razeni_Click()

Dim smer1 As Byte, smer2 As Byte

    If optb_vzest_1 Then

        smer1 = xlAscending

    Else

        smer1 = xlDescending

    End If

     If optb_vzest_2 Then

            smer2 = xlAscending

        Else

            smer2 = xlDescending

        End If

    Sheets(«data»).Select

    Range(«C6»).Select

    ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Clear

    If check_razeni = False Then

        ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Add Key:=Range(cmbx_klic1), _

        SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal

        Range(cmbx_klic1).Select

    Else

       ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Clear

       ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Add Key:=Range(cmbx_klic1), _

        SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal

        ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Add Key:=Range(cmbx_klic2), _

        SortOn:=xlSortOnValues, Order:=smer2, DataOption:=xlSortNormal

    End If

If cmbx_klic1 = cmbx_klic2 Then

        MsgBox «Oba zadané klíče jsou shodné. Zvolte odlišné.»: Exit Sub

End If

   With ActiveWorkbook.Worksheets(«data»).Sort

        .SetRange Range(«data»)

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range(cmbx_klic1).Select

    frm_menu.Hide

End Sub

Thanks a lot to everyone, who will try to help me.

Hello everyone, I have this error in my code (red part). I guess it´s a just a little mistake, but I can´t find it. Maybe someone can help me.

It is made on CommandButton for sort by two keys and I remake it from my professor’s code (and change name of lists ect.) and insert some condions, but it doesn’t work.

Where cmbx_klic1 is ComboBox for first key and cmbx_klic2 is ComboBox for second key for names by what you can sort it. And optb_vzest_1 is OptionButton for ascending for first key and optb_vzest_2 for ascending for second key. The last is check_razeni — CheckBox for not haveing one key for sorting, but two keys. And the list have really the same name as the part of the list.

Here the Code so far:

Private Sub cmdb_razeni_Click()

Dim smer1 As Byte, smer2 As Byte

    If optb_vzest_1 Then

        smer1 = xlAscending

    Else

        smer1 = xlDescending

    End If

     If optb_vzest_2 Then

            smer2 = xlAscending

        Else

            smer2 = xlDescending

        End If

    Sheets(«data»).Select

    Range(«C6»).Select

    ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Clear

    If check_razeni = False Then

        ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Add Key:=Range(cmbx_klic1), _

        SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal

        Range(cmbx_klic1).Select

    Else

       ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Clear

       ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Add Key:=Range(cmbx_klic1), _

        SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal

        ActiveWorkbook.Worksheets(«data»).Sort.SortFields.Add Key:=Range(cmbx_klic2), _

        SortOn:=xlSortOnValues, Order:=smer2, DataOption:=xlSortNormal

    End If

If cmbx_klic1 = cmbx_klic2 Then

        MsgBox «Oba zadané klíče jsou shodné. Zvolte odlišné.»: Exit Sub

End If

   With ActiveWorkbook.Worksheets(«data»).Sort

        .SetRange Range(«data»)

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range(cmbx_klic1).Select

    frm_menu.Hide

End Sub

Thanks a lot to everyone, who will try to help me.

 Summary:

In this post, I have included the complete information about Excel runtime error 1004. Besides that I have presented some best fixes to resolve runtime error 1004 effortlessly.

To fix Runtime Error 1004 in Excel you can take initiatives like uninstalling Microsoft Work, creating a new Excel template, or deleting The “GWXL97.XLA” File. If you don’t have any idea on how to apply these methods then go through this post.

Here in this article, we are going to discuss different types of VBA runtime error 1004 in Excel along with their fixes.

What Is Runtime Error 1004 In VBA Excel?

Excel error 1004 is one such annoying runtime error that mainly encounters while working with the Excel file. Or while trying to generate a Macro in Excel document and as a result, you are unable to do anything in your workbook.

This error may cause serious trouble while you are working with Visual Basic Applications and can crash your program or your system or in some cases, it freezes for some time. This error is faced by any versions of MS Excel such as Excel 2007/2010/2013/2016/2019 as well.

To recover lost Excel data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Error Detail:

Error Code: run-time error 1004

Description: Application or object-defined error

Screenshot Of The Error:

run-time error 1004

Don’t worry you can fix this Microsoft Visual Basic runtime error 1004, just by following the steps mentioned in this post. But before approaching the fixes section catch more information regarding runtime error 1004.

Excel VBA Run Time Error 1004 Along With The Fixes

EXCEL ERRORS

The lists of error messages associated with this Excel error 1004 are:

  1. VB: run-time error ‘1004’: Application-defined or object-defined error
  2. Excel VBA Runtime error 1004 “Select method of Range class failed”
  3. runtime error 1004 method range of object _global failed visual basic
  4. Excel macro “Run-time error ‘1004″
  5. Runtime error 1004 method open of object workbooks failed
  6. Run time error ‘1004’: Method ‘Ranger’ of Object’ Worksheet’ Failed
  7. Save As VBA run time Error 1004: Application defined or object defined error

Let’s discuss each of them one by one…!

#1 – VBA Run Time Error 1004: That Name is already taken. Try a different One

This VBA Run Time Error 1004 in Excel mainly occurs at the time of renaming the sheet.

If a worksheet with the same name already exists but still you are assigning that name to some other worksheet. In that case, VBA will throw the run time error 1004 along with the message: “The Name is Already Taken. Try a different one.”

VBA Run Time Error 1004 in Excel 1

Solution: You can fix this error code by renaming your Excel sheet.

#2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed

This VBA error code mainly occurs when someone tries to access the object range with wrong spelling or which doesn’t exist in the worksheet.

Suppose, you have named the cells range as “Headings,” but if you incorrectly mention the named range then obviously you will get the Run Time Error 1004: Method “Range” of object’ _ Global’ failed error.

VBA Run Time Error 1004 in Excel 2

Solution: So before running the code properly check the name of the range.

# 3 – VBA Run Time Error 1004: Select Method of Range class failed

This error code occurs when someone tries to choose the cells from a non-active sheet.

 Let’s understand with this an example:

Suppose you have selected cells from A1 to A5 from the Sheet1 worksheet. Whereas, your present active worksheet is Sheet2.

At that time it’s obvious to encounter Run Time Error 1004: Select Method of Range class failed.

VBA Run Time Error 1004 in Excel 3

Solution: To fix this, you need to activate the worksheet before selecting cells of it.

#4 – VBA Runtime Error 1004 method open of object workbooks failed

This specific run time Error 1004 arises when someone tries to open an Excel workbook having the same workbook name that is already open.

In that case, it’s quite common to encounter VBA Runtime Error 1004 method open of object workbooks failed.

VBA Run Time Error 1004 in Excel 4

Solution: Well to fix this, first of all close the already opened documents having a similar name.

#5 – VBA Runtime Error 1004 Method Sorry We Couldn’t Find:

The main reason behind the occurrence of this VBA error in Excel is due to renaming, shifting, or deletion of the mentioned path.

The reason behind this can be the wrong assigned path or file name with extension.

When your assigned code fails to fetch a file within your mentioned folder path. Then you will definitely get the runtime Error 1004 method. Sorry, and We couldn’t find it.

VBA Run Time Error 1004 in Excel 5

Solution: make a proper check across the given path or file name.

#6 – VBA Runtime Error 1004 Activate method range class failed

Behind this error, the reason can be activating the cells range without activating the Excel worksheet.

This specific error is quite very similar to the one which we have already discussed above i.e Run Time Error 1004: Select Method of Range class failed.

VBA Run Time Error 1004 in Excel 6

Solution: To fix this, you need to activate your excel sheet first and then activate the sheet cells. However, it is not possible to activate the cell of a sheet without activating the worksheet.

Why This Visual Basic Runtime Error 1004 Occurs?

Follow the reasons behind getting the run time error 1004:

  1. Due to corruption in the desktop icon for MS Excel.
  2. Conflict with other programs while opening VBA Excel file.
  3. When filtered data is copied and then pasted into MS Office Excel workbook.
  4. Due to application or object-defined error.
  5. A range value is set programmatically with a collection of large strings.

Well, these are common reasons behind getting the VBA runtime error 1004, now know how to fix it. Here we have described both the manual as well as automatic solution to fix the run time error 1004 in Excel 2016 and 2013. In case you are not able to fix the error manually then make use of the automatic MS Excel Repair Tool to fix the error automatically.

Fix Runtime Error 1004

Follow the steps given below to fix Excel run time error 1004 :

1: Uninstall Microsoft Work

2: Create New Excel Template

3: Delete The “GWXL97.XLA” File

Method 1: Uninstall Microsoft Work

1. Go to the Task Manager and stop the entire running programs.

2. Then go to Start menu > and select Control Panel.

run time error 1004 (1)

3. Next, in the Control Panel select Add or Remove Program.

run time error 1004 (2)

4. Here, you will get the list of programs that are currently installed on your PC, and then from the list select Microsoft Work.

run time error 1004

5. And click on uninstall to remove it from the PC.

It is also important to scan your system for viruses or malware, as this corrupts the files and important documents. You can make use of the best antivirus program to remove malware and also get rid of the runtime error 1004.

Method 2: Create New Excel Template

Another very simple method to fix Excel runtime error 1004 is by putting a new Excel worksheet file within a template. Instead of copying or duplicating the existing worksheet.

Here is the complete step on how to perform this task.

1.Start your Excel application.

2. Make a fresh new Excel workbook, after then delete the entire sheets present on it leaving only a single one.

3. Now format the workbook as per your need or like the way you want to design in your default template.

4. Excel 2003 user: Tap to the File>Save As option

SAVE EXCEL FILE

OR Excel 2007 or latest versions: Tap to the Microsoft Office button after then hit the Save As option.

SAVE EXCEL FILE 1

5. Now in the field of File name, assign name for your template.

6. On the side of Save as type there is a small arrow key, make a tap on it. From the opened drop menu

  • Excel 2003 users have to choose the Excel Template (.xlt)

Create New Excel Template 1

  • And Excel 2007 or later version have to choose the Excel Template (.xltx)

Create New Excel Template 2

7. Tap to the Save.

8. After the successful creation of the template, now you can programmatically insert it by making use of the following code:
Add Type:=pathfilename

Remarks: 

From the above code, you have to replace the pathfilename with the complete path including the file name. For assigning the exact location of the sheet template you have just created.

Method 3: Delete The “GWXL97.XLA” File

Follow another manual method to fix Excel Runtime Error 1004:

1. Right-click on the start menu.

2. Then select the Explore option.

Excel Runtime Error 1004

3. Then open the following directory – C:Program FilesMSOfficeOfficeXLSTART

Excel Runtime Error 1004 (1)

4. Here you need to delete “GWXL97.XLA” file

Excel Runtime Error 1004 (2)

5. And open the Excel after closing the explorer

You would find that the program is running fine without a runtime error. But if you are still facing the error then make use of the automatic MS Excel Repair Tool, to fix the error easily.

Automatic Solution: MS Excel Repair Tool

MS Excel Repair Tool is a professional recommended solution to easily repair both .xls and .xlsx file. It supports several files in one repair cycle. It is a unique tool that can repair multiple corrupted Excel files at one time and also recover everything included charts, cell comments, worksheet properties, and other data. This can recover the corrupt Excel file to a new blank file. It is extremely easy to use and supports both Windows as well as Mac operating systems.

* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

Conclusion:

Hope this article helps you to repair the runtime error 1004 in Excel and recovers Excel file data. In this article, we have provided a manual as well as automatic solution to get rid of Excel run-time error 1004. You can make use of any solution according to your desire.

Good Luck!!!

Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.

VBA 1004 Error is a runtime error in VBA. It is also known as application-defined or object-defined error. Why is that? Because we have a limited number of columns in Excel. When our code gives the command to go out of range, we get a 1004 Error. There are other situations when we get this error when we refer to a range that does not exist in the sheet.

VBA Error 1004 in Excel

VBA 1004 Error is a run time error in VBA and occurs while running the code. Errors are part and parcel of the coding, especially when writing for the first time. Therefore, you may come across many errors in VBA. However, this is common for everybody, and there is no big deal about it.

However, knowing the error of why it is coming makes you avoid those mistakes in the coming future.

In this article, we will discuss one of the important errors in Excel, “VBA 1004 Error”.

Table of contents
  • VBA Error 1004 in Excel
    • Top 6 Excel VBA 1004 Runtime Errors
      • #1 – VBA Run Time Error 1004: That Name is already taken. Try a different One:
      • #2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed:
      • # 3 – VBA Run Time Error 1004: Select Method of Range class failed:
      • #4 – VBA Runtime Error 1004 method open of object workbooks failed:
      • #5 – VBA Runtime Error 1004 method Sorry We couldn’t Find:
      • #6 – VBA Runtime Error 1004 Activate method range class failed:
    • Recommended Articles

VBA 1004 Error

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA 1004 Error (wallstreetmojo.com)

Top 6 Excel VBA 1004 Runtime Errors

You can download this VBA 1004 Error Template here – VBA 1004 Error Template

#1 – VBA Run Time Error 1004: That Name is already taken. Try a different One:

This error occurs while renaming the sheet.

If the worksheet’s name already exists. If you try to assign the same name to another sheet, VBA throws a “Run-time error ‘1004.’ ”

Look at the below code.

Code:

Sub Error1004_Example()

 Worksheets("Sheet2").Name = "Sheet1"

End Sub

VBA 1004 Error Example 1

We are trying to rename sheet 2 as sheet 1. But, we already have a sheet named “Sheet1”.

VBA 1004 Error Example 1-2

If we run this code using the F5 key or manually, we will get “Run-time error ‘1004’ “: “That name is already taken. Try a different one.”

VBA 1004 Error Example 1-1

So, try renaming the sheet accordingly.

#2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed:

It usually occurs when we try to access the named range in excelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more with a spelling mistake or that does not exist you are referring to.

For this, we have named the range of cells “Headings,” as shown in the below image.

VBA 1004 Error Example 2

Now, by using the RANGE object, we can access this range.

Code:

Sub Error1004_Example()

  Range("Headings").Select

End Sub

VBA 1004 Error Example 2-1

If you run this code by pressing the F5 key, then this code will select the named range.

VBA 1004 Error Example 2-2

But, if we mention the named range wrongly, we will get “Run-time error ‘1004.’ “Method “Range” of object’ _ Global” failed.

Code:

Sub Error1004_Example()

  Range("Headngs").Select

End Sub

VBA 1004 Error Example 2-3

Run this code manually or use the F5 key and see the result.

VBA 1004 Error Example 2-4

# 3 – VBA Run Time Error 1004: Select Method of Range class failed:

It usually occurs when we try to select the cells other than the active sheet without making the sheet select or active.

 Look at the below code.

Code:

Sub Error1004_Example()

  Worksheets("Sheet1").Range("A1:A5").Select

End Sub

VBA 1004 Error Example 3

The above code says to select the cells A1 to A5 in the worksheet “Sheet1”. However, to experiment, my present active sheet is “Sheet2”, not “Sheet1”.

We will run this code using the F5 key or manually to see what happens.

VBA 1004 Error Example 3-1

We got “Run-time error ‘1004.’ “Select method of Range class failed” as without activating the sheet; we try to select the cells of that sheet. So first, we need to activate the sheet before we select the cells. Below is the correct code.

#4 – VBA Runtime Error 1004 method open of object workbooks failed:

It usually occurs when you try to open the workbook, which has the same name as the other workbook we have already opened.

Look at the below code.

Code:

Sub Error1004_Example()

   Dim wb As Workbook
    Set wb = Workbooks.Open("FileName.xls", ReadOnly:=True, CorruptLoad:=xlExtractData)

End Sub

Example 3-2

It will throw the below error.

Example 3-3

#5 – VBA Runtime Error 1004 method Sorry We couldn’t Find:

This error occurs when you try to open the file which does not exist in the mentioned path. For example, it could move, be renamed, or deleted from the mentioned path. One reason for this is the wrong type of path or file name with the excel extensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more.

Now, take a look at the below code.

Code:

Sub Error1004_Example()

   Workbooks.Open Filename:="E:Excel FilesInfographicsABC.xlsx"

End Sub

Example 4

This code says to open the file “ABC.xlsx” in the mentioned folder path.

We know there is no file in the mentioned folder path. So, we will get the: Run-time error ‘1004’ method when no file exists in the mentioned folder. Sorry, and We couldn’t find it.

Example 4-1

#6 – VBA Runtime Error 1004 Activate method range class failed:

This error occurs mainly due to activating the range of cells without activating the worksheet.

Look at the below code.

Code:

Sub Error1004_Example()

  Worksheets("Sheet1").Range("A1:A5").Activate

End Sub

Example 5

This error is similar to the one we have seen in Run-time error’ 1004′: Select method of Range class failed.

If we run manually or use the F5 key, we will get the below error.

Example 5-1

Because without activating the sheet, we cannot activate the cells in it. So first, activate the sheet and then activate the cells of that sheet.

Recommended Articles

This article has been a guide to VBA 1004 Error. Here, we discuss the top 6 types of 1004 run-time errors in VBA and how to fix them, along with examples and downloadable templates. Below are some useful articles related to VBA: –

  • VBA LEN Function
  • VBA Type Mismatch Error
  • VBA Sub Procedures
  • VBA Match Function

VBA 1004 Error

Excel VBA Error 1004

VBA 1004 Error is an error we encounter while we execute a code in VBA it is also known as VBA Runtime error. While we work in VBA or in any other programming language or even in our daily work we encounter different kinds of errors. Sometimes even we miss a single character in the code which causes the whole code not to work or maybe the entire code is wrong.

Errors are definitely a part of the code we write. It may be unintentional but they exist. No matter how pro we are in coding, runtime error can occur anywhere. As explained above VBA 1004 Error is an error which occurs during the runtime of the code in excel. It is also called an application defined or object defined error.

There are different types of reasons we get VBA Runtime Error 1004 in excel, let us learn a few of them.

  • VBA Runtime Error 1004: Method ‘Range’ of object ‘_ Global’ failed:

This error occurs when the range value we refer to VBA is incorrect. It is also called as Method “Range” of object’ _ Global’ failed.

  • VBA Run Time Error 1004: That Name is already taken. Try a different One:

We give the same name to a worksheet which is already taken by another worksheet.

  • VBA Runtime Error 1004: Unable to get the select property of Range class:

This is an error when we select a range in another worksheet without activating the worksheet we are referring to.

  • VBA Runtime Error 1004: Method ‘Open’ of object ‘Workbooks’ failed:

This error occurs when we try to open a workbook which is already open or the file is used by another program already.

  • VBA Runtime Error 1004: Sorry We Couldn’t Find:

We get this error when we try to open a worksheet which doesn’t exist.

As we have learned there can be various reasons we get a runtime error. Runtime error can occur at any line of code. We need to learn how to learn to handle these errors and it is called VBA Error Handling.

Example of VBA Runtime Error 1004 in Excel

Now as I have described different types of error which can occur during runtime of any VBA code now let us learn them how they appear with examples.

You can download this VBA 1004 Error Excel Template here – VBA 1004 Error Excel Template

VBA Runtime Error 1004 – Example #1

As explained about this error, this error occurs when we refer to an incorrect named range value in VBA. This can happen if we make a spelling mistake of the named range of to refer a range that doesn’t even exist. To demonstrate this let us make a named range first. I have the following data here.

VBA Error 1004 Example 1

  • Let us name this table header as DATA.

VBA Error 1004 Example 1-1

  • Go to the Developer tab click on Visual Basic to Open VB Editor.

VBA Error 1004 Example 1-2

  • Declare the sub-function to start writing the code.

Code:

Sub Sample()

End Sub

VBA Error 1004 Example 1-3

  • Call the header we named by the following code written below.

Code:

Sub Sample()

Range("Data").Select

End Sub

VBA Error 1004 Example 1-4

  • When we run the code we can see in the excel that it has been selected as we have called the header correctly.

Result of Example 1-4

  • Now we misspell the spelling for the header name.

Code:

Sub Sample()

Range("Dataa").Select

End Sub

VBA Error 1004 Example 1-5

  • Run the code again to see the result.

Result of Example 1-6

We get excel VBA Runtime Error 1004 because we have misspelled the range name.

VBA Runtime Error 1004 – Example #2

We get this error when we try to rename a worksheet with a name which is already taken. For example, I have renamed sheet 1 as “Anand” and I will try to rename sheet 2 as same then see the result.

VBA Error 1004 Example 2

  • Go to the Developer tab click on Visual Basic to Open VB Editor.
  • Declare a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

VBA Error 1004 Example 2-2

  • Try to rename sheet 2 as Anand by the following code below,

Code:

Sub Sample1()

Worksheets("Sheet2").Name = "Anand"

End Sub

VBA Error 1004 Example 2-3

  • Run the above code and see the result.

Result of Example 2

When I try to rename a sheet with the name which is already taken I get an Error.

VBA Runtime Error 1004 – Example #3

I will try to add the value from sheet 2 to a variable in sheet 3. But I will not activate the sheet 2 and see what happens.

  • Go to the Developer tab click on Visual Basic to Open VB Editor.
  • Declare a sub-function to start writing the code.

Code:

Sub Sample2()

End Sub

VBA Error 1004 Example 3-2

  • Declare two variables A and B as an integer.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer

End Sub

VBA Error 1004 Example 3-3

  • In Variable B store the value of A in addition to cell A1 of sheet 2.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer
B = A + Worksheets("Sheet2").Range("A1").Select

End Sub

VBA Error 1004 Example 3-4

  • Let us suppose the code works and use msgbox function to display the value of B.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer
B = A + Worksheets("Sheet2").Range("A1").Select
MsgBox B

End Sub

VBA Error 1004 Example 3-5

  • Run the code to see the result obtained.

Result of Example 3

We get this Error because we have not activated sheet 2 but we are trying to use a value of sheet 2.

VBA Runtime Error 1004 – Example #4

We encounter this runtime error when we have already the same name of workbook open but we try to open it again.

For this example, I have already renamed my workbook as VBA 1004 Error.xlsm and I will try to open it again which is already open and see if I get VBA 1004 Error.

  • Go to the Developer tab click on Visual Basic to Open VB Editor.
  • Declare a sub-function to start writing the code.

Code:

Sub Sample3()

End Sub

VBA Error 1004 Example 4-2

  • Declare a variable as the workbook.

Code:

Sub Sample3()

Dim A As Workbook

End Sub

VBA Error 1004 Example 4-3

Try to open the workbook we have currently already open with the following code.

Code:

Sub Sample3()

Dim A As Workbook

Set wb = Workbooks.Open("\VBA 1004 Error.xlsm", ReadOnly:=True, CorruptLoad:=xlExtractData)

End Sub

VBA Error 1004 Example 4-4

Run the above code to see the result.

Result of Example 4

We get this error because we have already opened the same workbook already.

VBA Runtime Error 1004 – Example #5

We get this error when we try to open a workbook which doesn’t exist. This is somewhat similar to the above error we get as VBA cannot find the workbook.

  • Go to the Developer tab click on Visual Basic to Open VB Editor.
  • Declare a sub-function to start writing the code.

Code:

Sub Sample4()

End Sub

Example 5-1

  • Try Open any workbook with the following code,

Code:

Sub Sample4()

Workbooks.Open Filename:="C:EDUCBA ContentAprilVBA OR Function.xlsm"

End Sub

Example 5-2

  • I have already deleted the sheet from the location.
  • Run the code to see the result.

Result of Example 5-3

As the sheet doesn’t exist at the given location we get this error.

Things to Remember

  • Always check for spelling mistakes.
  • Do not rename multiple worksheets with the same name.
  • Before calling any other reference to be sure to activate the respective worksheet.
  • Before trying to open any other worksheet ensure the path provided is correct.

Recommended Articles

This has been a guide to VBA 1004 Error. Here we discussed Excel VBA Runtime Error 1004 along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA LBound
  2. VBA While Loop
  3. VBA IsError
  4. VBA CLng

VBA Runtime Error 1004 occurs while you are executing a macro in Excel. It’s an error that can occur due to several reasons. In the below example, as you can see, when I run the code, it shows the run-time error ‘1004’. In simple words, you can also say it occurs when you can’t perform an activity with the object or the application.

In this tutorial, we will understand several other reasons for this runtime 1004 error.

Error1: VBA Run Time Error 1004: That Name is already taken. Try a different One:

As I shared above, when you try to add a sheet with the name that is already there in the workbook using a VBA code, VBA shows you a run-time error with the 1004 code.

Even when you try to rename a worksheet, you’ll get the same error from VBA.

In the above code, I tried to rename the sheet “Sheet2” with the name “Sheet1”, but as you can see, I already have the “Sheet1” in the workbook; VBA showed me the error code 1004.

To deal with this problem is to check if a sheet already exists in the workbook or not.

Error2: VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed:

As you know, you can create a named range in Excel, but when you try to refer to that named range using a VBA code, and you misspelled its name, you are likely to get the run-time error 1004 Method “Range” of object’ _ Global’ failed.

In the following example, I have a named range with the name “myRange”, but while writing the macro, I have misspelled it as “myRang” even though it’s a typing mistake; VBA is not that smart to identify this mistake, and refer to the correct named range.

There is no named range with the name that I have mentioned, and that is why it showed me the run-time error 1004.

Error3: VBA Run Time Error 1004: Select Method of Range class failed:

When you use a VBA code to select a range that is not in the active worksheet, VBA will show you the run-time error 1004. Because, if you want to select a cell or range of cells for a particular worksheet needs to be active at that point.

You cannot select a cell from sheet1 if sheet2 is active right now.

As you can see in the following example, I have made the same mistake where I’m referring to cell A1 from sheet one, but sheet2 is active.

The best way to deal with this error is to activate the sheet first and then write a line of code that selects a cell or the range from that sheet. You can also check if a sheet is active or not and then select the cell from that sheet, and if somehow that sheet is not active, you can show a message to the user to activate that sheet first.

Error4: VBA Runtime Error 1004 Activate method range class failed:

Just like the above reason, when you try to activate a cell or range of cells from a specific worksheet that is not active at the moment, we will show you runtime error 1004 (Activate method range class failed:).

In the about example when I tried to activate cell A1 in the sheet1 it gave me runtime error 1004, is the active sheet was the sheet2.

To deal with this error again you need to verify that the worksheet that you are referring to is active or not. Or you can also verify if that sheet is there in the workbook or not.

Error5: VBA Runtime Error 1004 method Sorry We couldn’t Find:

If you’re trying to open an Excel file using a path, but that workbook is not there, maybe it has been moved, deleted, or renamed. In that case, VBA will again show you a run-time error of 1004.

That’s what happened in the following example where I tried to open the file with the path from my desktop, but that file is not there, and it has shown me an error.

To deal with the situation, you can use a VBA code to verify if the file that you are referring to is there in the folder or not.

Error6: VBA Runtime Error 1004 method open of object workbooks failed:

This error is also related to the workbook. So when you try to open a workbook with the name for which a book already exists and is open at the moment, VBA will show you the run-time error 1004 (method open of object workbooks failed).

The same thing happened in the above example, where I tried to open a workbook that is already open, and VBA showed me a run time error (1004).

How to Correct VBA’s Runtime (Error 1004)

Error 1004 is a run-time error that occurs while executing a VBA code. And all the errors that we have discussed above are due to human errors while writing the code, following the points you can use to avoid the run-time error 1004.

  1. Do a step-by-step code execution to see on which line the error occurs.
  2. Make sure to verify all the names of the workbooks that you are referring to.
  3. You can also use a VBA code to verify if a workbook is already opened or not.
  4. And use the activate method to activate a worksheet before you refer to a cell or range from it.

More on VBA Errors

Subscript Out of Range (Error 9) | Type Mismatch (Error 13) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call Or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400

Понравилась статья? Поделить с друзьями:
  • Ошибка unknown hard error windows 10 как исправить
  • Ошибка vanos выпуск
  • Ошибка warzone 2 0x00001338 11571 n
  • Ошибка unknown error перевод
  • Ошибка vanos впуск n52