Method range of object global failed vba как исправить

 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.

Error Detail:

Error Code: run-time error 1004

Description: Application or object-defined error

Screenshot Of The Error:

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

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.”

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

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)

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

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.

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

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

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!!!

Mr. Mickle

Thank you for the new tip.  A new horizon….

the final SUB would be below. ( It worked nicely )

(it is case sensitive)

many many thanks

Sub RemoveWordTotal()
    Application.ScreenUpdating = False
    Dim i As Long
    For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
        Cells(i, "B") = Application.Substitute(Cells(i, "B"),"Total","")
    Next i
    Application.ScreenUpdating = True
End Sub
The above  can also be used to replace a text instead of just blank. 
instead of "" - replace it with "New Text" !!
VERY VERSATILE CODE, Mr. Mickle.. 
FOR OTHERS WHO MIGHT FIND THIS USEFUL:
maybe called as function (BELOW)  >  Call ReplaceSomeText("Total", "B", "")
"Total" (mWRD) = the word within the cell to be replaced
"B" (mcol) = the column referenced
"" (newTXT) = blank or any text to replace mWRD

Sub ReplaceSomeText(mWRD As String, mcol As String, newTXT As String)
   Application.ScreenUpdating = False
   Dim i As Long
   For i = Cells(Rows.Count, mcol).End(xlUp).Row To 2 Step -1
       Cells(i, mcol) = Application.Substitute(Cells(i, mcol),mWRD, newTXT)
   Next i
   Application.ScreenUpdating = True
End Sub



  


                       
  







  


                       
  

Method Range of object _global failed error says that you aren’t satisfying the argument expectations of the Range() method. The given method asks for a particular type of input, and its demand needs to be fulfilled irrespective of your coding logic.

As there can be different coding scenarios, the following post will discuss the argument expectations of the Range() method and tell you about different ways through which you can fix the said error. So, spend the next few minutes reading this article, and you’ll be thankful to have found this comprehensive guide.

Why Is the Method Range of Object _global Failed Error Arising?

The method Range of object ‘global’ failed VBA runtime error is arising due to the unfulfilled argument expectations of the Range() method. If you don’t provide a correct range reference to the given method, the method call will fail, and you’ll see the same error occurring on your PC.

– An Unexpected Range Reference

Providing an unexpected or wrong range reference to the Range() method can become a reason for the method’ range of object _global failed Excel VBA error. It will mostly occur when you pass a combination of the row and column numbers to the Range() method.

Also, you can say that the situations where you don’t pass the range directly and expect some processed numeric values to create your desired range of cells are most likely to lead you to the same error. However, it doesn’t mean that you shouldn’t do any processing beforehand and always go for the direct approach. All that matters is the correctness of the resulting range reference, where a single cell is depicted by a column letter and a row number.

Look at this example to gain clarity. You have created a function where you process some values and create variables like targetRow and targetColumn containing row and column numbers. Now, you want to target a particular cell by passing the said variables to the Range() method. Here, you might face the given run-time error because both of the variables together result in a numeric value, while the given method expects the column to be represented by an alphabet or letter.

How To Fix Method Range of Object _global Failed Error?

You can fix the method ‘range’ of object failed VBA error by passing a correct reference range to the Range() method, or switching to the Cells() method. To satisfy the Range() method, you can use the R1C1 reference style or leverage the Address property of the same method.

– Offer the Correct Range Reference

You should offer a correct range reference to the Range() method to ensure that the method call doesn’t fail and you don’t get the stated error on your system. So, to satisfy the given method, you’ll have to specify a column letter and a row number to represent each cell.

Talking about the example discussed earlier. You’ll need to ensure that after performing your desired operations, the variable targeting a particular column contains an alphabet, while the targetRow variable contains a number. This way even if your Range() method call looks like Range(targetColumn & targetRow), you’ll not get the method’ range of object _global’ failed table error.

– Work With the R1C1 Notation

The R1C1 notation can help you use the column number instead of the column letter flawlessly. It will work like extra information provided to the Range() method about the variables passed to it. So, you’ll only need to precede the variables with R and C to fix the error.

It won’t be wrong to say that introducing the R1C1 notation to your Range() method argument will reduce the hassle of converting the column number to a column letter, as suggested by the previous solution. Here you go with a code snippet that adds the said notation to the Range() method call.

Range(“R” & targetRow & “C” & targetColumn)

Now, the stated method will know that the number stored in the targetRow is the number of the row, and the number stored in the targetColumn is the column number. Consequently, it won’t be confused anymore, and you’ll not get the given error. To enhance your knowledge, note that R1C1 notation is also known as the R1C1 reference style.

– Replace Range() With Cells()

If you are trying to obtain or place the data in a particular cell by using the Range() method, and the error is constantly disturbing you, then you should switch to the Cells() method. The Cells() method will make it easier for you to target the row and column numbers.

The given method accepts row number and column number separated by a comma and lets you target a particular cell. It means that you’ll neither need to use the R1C1 reference style nor convert the column number into the column letter. All that you’ll have to do is to execute the Cells method and say no to the Range() method.

You can look at the implementation of the Cells() method below:

Cells(targetRow,targetColumn)

– Leverage the Address Property of the Range() Method

You can use the Address property of the Range() method to kill the method ‘range’ of object’_worksheet’ failed 1004 error on the go. The said property can be helpful when your code calls the Cells() method as an argument of the Range() method.

Think about it this way. You have written your VBA code in such a way that the Range() method receives a call to the Cells() method as an argument. Now, although the latter method returns the expected cell address, it will not be acceptable by the former method unless you use the Address property.

Here is how you can use the Address property with the combo of Range() and Cells() methods:

Ws.Range(Ws.Cells(targetRow, targetColumn).Address)

FAQ

1. What Causes the Method Open Object Workbooks Failed Error?

The method Open object of workbooks failed error might be caused by the corrupted temporary files or an Access file referencing an Excel file in the same OneDrive folder. Also, there can be other reasons depending on your code block.

2. What Does the Select Method of Range Class Failed Error Mean?

The Select method of Range class failed means that you are trying to select the cells without activating the worksheet where they are present. It’s like ignoring the sheet activation or selection step and jumping to the cell selection step directly.

3. How To Fix Method ‘ExecuteExcel4Macro’ of Object’_global’ Failed?

You can fix the method ‘ExecuteExcel4Macro’ of object’_global’ failed error by specifying the workbook and worksheet references. It will work because Microsoft Excel 4.0 macro is successfully evaluated in the external worksheet or workbook and can’t be used in the context of the current worksheet.

4. How To Resolve the Object Does Not Exist Error?

You can resolve the VBA Error 1004: Object does not exist error by creating the range names in the Excel workbook. Also, referring to the range in the traditional row and column format like Range(“A1:B10”) will help fix the error.

5. What Are the Steps To Name a Range in VBA?

You can name a range by defining a variable as Range and setting it to your desired range of cells. Next, you’ll have to access the Names property of ThisWorkbook object. Later, you’ll have to specify the Name argument and the range variable as the RefersTo argument.

Conclusion

According to the above post, the method range of object _global failed error occurs as a signal showing that you haven’t passed a valid reference range to the Range() method. Remember that the Range() method accepts a column letter and a row number and will throw this error when it doesn’t see any letter that would indicate the target column. Please read the below listicle to grab the important details from the article quickly.

• You should double-check the arguments being passed to the Range method VBA and ensure that it’s receiving a valid column letter followed by a valid row number to resolve the error on your screen.
• You can use the R1C1 notation or reference style to clarify the Range() method about the row and column numbers.
• It would be best to use the Cells() method when you want to target a specific cell with the help of a column number and row number.
• You can use the Address property of the Range() method to extract the acceptable cell address from the result of the Cells() method.

Now, you are all clear about the Range() method argument and can safely resume your work by beginning with resolving the said error.