12

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
  • 1
  • 1
Melissa Charn
  • 121
  • 1
  • 1
  • 3
  • 3
    What are the values of `inputTemplateContent` and `NO_ENTRY` when you get the error? Does it work with `Set Range(inputTemplateContent) = NO_ENTRY`? – assylias Aug 29 '12 at 09:29
  • Just in case anyone has the same problem: if you use Range specifying book and sheet and you end with this error, check book's name doesn't have white spaces (maybe it's obvious but if you have legacy Macros and you have never used VBA for Excel this can be tricky). – EAmez Feb 07 '18 at 12:27

1 Answers1

32

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.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73