The code doesn't know which worksheet CELLS is locat on. Use this instead

with Workbooks("Workbook1").Sheets("Sheet1")
Set MyRangeName = .Range(.Cells(12, 12), .Cells(15, 12))
end with

Notice the dot I put in front of CELLS


"needVBAhelp" wrote:

I am trying to set a range name using the R1C1 format. Here is what I have
tried

Dim MyRangeName as Range
Set MyRangeName = Workbooks("Workbook1").Sheets("Sheet1").Range(Cell s(12,
12), Cells(15, 12))

The above line produces the following error: "Application-defined or
Object-defined error".

What I find strange is the above macro works fine if I replace the above with:
Set MyRangeName = Workbooks("Workbook1").Sheets("Sheet1").Range("L12 :L15")

For reasons that I believe are not relevant, I need to use the R1C1 method.

Any suggestions would be much appreciated.

--
needVBAhelp