CODEWITHSUNDEEP
vbaexcel
BillD
BillD

Reputation: 147

1004 Method 'Range' of object '_Worksheet' failed

The following assignment statement is in the code for a commandbutton on a form:

Range("AvailabilityDataModified").Value = "No"

AvailabilityDataModified is a single-celled named range on a sheet called "Controls". This statement executes properly with no error.

I also have three occurrences of the following statement (virtually identical to the one above) that reside in a sheet's code for multiple event handlers:

Range("AvailabilityDataModified").Value = "Yes"

My problem is the 3 occurrences of the 2nd instance of code generate the 1004 Method 'Range' of object '_Worksheet' failed error while the first does not. This might be a problem with scope; however, I don't believe you need any additional reference info when assigning a value to a named range. I'm at a loss at this point.

Upvotes: 0

Views: 1743

Answers (1)

Siphor
Siphor

Reputation: 2544

Use 

Worksheets("Controls").Range("AvailabilityDataModified").Value = "Yes"

instead.
Inside a worksheet object Range refers to SheetName.Range, not to the workbook-scoped Excel.Application.Range object. This causes the range "AvailabilityDataModified" to be restricted to the sheet of the worksheet object. Because no cells of "AvailabilityDataModified" are in the sheet the Range returns a error.

Upvotes: 4

Related Questions