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?
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 extension.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With