Setting Range Name using R1C1

[Guest]

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(Cells(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.

 

[Guest]

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

 

[Guest]

Joel,

Worked great!! Thanks so much!!!