I’ve tried searching the repository for the solution to the above problem but my VBA shills are rudimentary and the answer eludes me. Any guidance would be greatly appreciated.
When running the code below it errows at the following line:
PartNumber = Range("E" & myrow).Value
The code is Run time error ‘1004’:
Method ‘Range of object’_Global failed.
Dim objDrv As Object
Dim strMsg As String
For Each objDrv In CreateObject("Scripting.FileSystemObject").drives
Select Case objDrv.DriveType
Case 0: strMsg = strMsg & vbNewLine & objDrv.driveletter & ": Unknown"
Case 1: strMsg = strMsg & vbNewLine & objDrv.driveletter & ": Removable Drive"
Case 2: strMsg = strMsg & vbNewLine & objDrv.driveletter & ": Hard Disk Drive"
Case 3: strMsg = strMsg & vbNewLine & objDrv.driveletter & ": Network Drive"
'Case 4: strMsg = strMsg & vbNewLine & objDrv.driveletter & ": CDROM Drive"
Case 5: strMsg = strMsg & vbNewLine & objDrv.driveletter & ": RAM Disk Drive"
End Select
Next
Set objDrv = Nothing
'MsgBox strMsg, vbInformation
Dim sdcard As String
sdcard = InputBox( _
"Confirm which drive you wnat to create the romdata directory in." & vbNewLine & vbNewLine & "Drives Found:" _
& strMsg & vbNewLine, _
"Choose SD Card Letter.", _
"Type your file name here")
If Len(sdcard) = 1 Then
MsgBox "You have selcted the " & sdcard & " drive."
ElseIf Len(sdcard) >= 2 Then
MsgBox " Too many characters"
ElseIf Len(sdcard) <= 0 Then
End If
Dim myLoop As Integer
For myLoop = 7 To 9000
If Len(Range("I" & myLoop).Value) > 0 Then
Dim aTemp As String
Dim bTemp As String
Dim dTemp As String
Dim eTemp As String
Dim subdir As String
Dim PartNumber As String
Dim otemp As String
Dim ptemp As String
aTemp = sdcard & Chr(58) & "\romdata\"
a1temp = sdcard & Chr(58) & "\romdata"
bTemp = strMsg
PartNumber = Range("E" & myrow).Value
otemp = PartNumber & ".fwu"
ptemp = "\" & PartNumber
dTemp = ActiveWorkbook.pATH
eTemp = "\Firmware files"
subdir = "\Firmware Files\" & PartNumber & "\" & otemp
MsgBox "The full path to this file is " & dTemp & "\" & PartNumber
If dir(dTemp & subdir) = "" Then
MsgBox "The file you have selected, (" & PartNumber & ") will not be copied." & vbNewLine & _
"Please check the file is suitable for uploading to the device using an SD card." & vbNewLine & _
"(It should have a .fwu extension.)"
Exit Sub
End If
MsgBox "The file " & otemp & " is being copied to " & aTemp
If Len(dir(aTemp, vbDirectory)) = 0 Then
MkDir a1temp
End If
FileCopy dTemp & subdir, aTemp & otemp
asked Apr 22, 2013 at 22:04
myrow
is never defined in the snippet you have posted. Define it before using it.
answered Apr 22, 2013 at 22:10
ApplePieApplePie
8,8245 gold badges39 silver badges60 bronze badges
2
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 SubThe 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
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?
asked Aug 29, 2012 at 9:26
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 KusleikaDick Kusleika
32.7k4 gold badges52 silver badges73 bronze badges
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:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- 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:
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:
- VB: run-time error ‘1004’: Application-defined or object-defined error
- Excel VBA Runtime error 1004 “Select method of Range class failed”
- runtime error 1004 method range of object _global failed visual basic
- Excel macro “Run-time error ‘1004″
- Runtime error 1004 method open of object workbooks failed
- Run time error ‘1004’: Method ‘Ranger’ of Object’ Worksheet’ Failed
- 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:
- Due to corruption in the desktop icon for MS Excel.
- Conflict with other programs while opening VBA Excel file.
- When filtered data is copied and then pasted into MS Office Excel workbook.
- Due to application or object-defined error.
- 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:=path\filename
Remarks:
From the above code, you have to replace the path\filename 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 Files\MSOffice\Office\XLSTART
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:
excel-repair-main-interface-1
stellar-repair-for-excel-select-file-2
stellar-repair-for-excel-repairing-3
stellar-repair-for-excel-preview-4
stellar-repair-for-excel-save-5
stellar-repair-for-excel-saving-6
stellar-repair-for-excel-repaired-7
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 a 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.
- Remove From My Forums
-
Question
-
I have data stored in an Access 97 database from which I am creating an excel spreadsheet. I have a code in Access that is counting the columns in the generated excel worksheet that contain data. It sometimes works but sometimes
I get the «Run-time error ‘1004’:Method ‘Columns’ of object’_Global’ failed». It sometimes failed on first run thru of the code but almost always fails when the code has generated a second workbook or a second sheet in the active workbook.
Excel version we are using is XP. Code is as follows:Dim stSample as Long
stSample = Cells(9, Columns.Count).End(xlToLeft).Column (statement that fails)
If stSample = 6 Then ‘format for one sampleI’ve also tried:
Dim stSample as Long
stSample = 0
stSample = stSample + Cells(9, Columns.Count).End(xlToLeft).Column
If stSample = 6 Then ‘format for one sample
Answers
-
You are apparently automating Excel from Access. You should refer ALL Excel objects back to the Excel.Application object that you create, either directly or indirectly.
For example, the following is OK, since both Cells and Columns refer bacvk to xlApp via xlWsh and xlWbk:
Dim xlApp As Excel.Application Dim xlWbk As Excel.Workbook Dim xlWsh As Excel.Worksheet Dim stSample As Long Set xlApp = CreateObject("Excel.Application") Set xlWbk = xlApp.Workbooks.Open("PathAndFilename.xls") Set xlWsh = xlWbk.Worksheets(1) stSample = xlWsh.Cells(9, xlWsh.Columns.Count).End(xlToLeft).Column
But if you use Cells or Columns without specifying what they belong to, you will probably create a second instance of Excel.Application that is not controlled by your code.
Regards, Hans Vogelaar
-
Marked as answer by
Thursday, March 14, 2013 7:14 AM
-
Marked as answer by