Using Cells Method to define Ranges

I have a question regarding refering to ranges. Below is some sample
(and simple) code to illustrate my question. I prefer to use R1C1
notation and the cells property. But I have to use the A1 notation
when I set the range "r" in the code below. Why can't I replace that
line with:

Set r = y.Range(cells(1,1),cells(3,3))

This line returns the " Method 'Range' or object '_Worksheet' failed "
error.

Thanks for the help,
John

Sub junk()

Dim xyz As Workbook
Dim x As Worksheet
Dim y As Worksheet
Dim z As Worksheet
Dim r As Range

Set xyz = ActiveWorkbook
abc.Activate
'
Set x = xyz.Worksheets("X")
Set y = xyz.Worksheets("Y")
Set z = xyz.Worksheets("Z")

Set r = y.Range("A1:C3")
r.Font.Bold = True

End Sub

The following worked for me when I dimensioned 'abc' and set it to
another workbook. See the following:

Sub junk()
Dim xyz As Workbook
Dim x As Worksheet
Dim y As Worksheet
Dim z As Worksheet
Dim r As Range

'assuming abc is a workbook
Dim abc As Workbook
Set abc = Workbooks("book1")

Set xyz = ActiveWorkbook
abc.Activate

Set x = xyz.Worksheets("X")
Set y = xyz.Worksheets("Y")
Set z = xyz.Worksheets("Z")

Set r = y.Range("A1:C3")
r.Font.Bold = True
End Sub

HTH--Lonnie M.

I need to work on my listening/reading skills I guess--sorry about
that.
Try activating the sheet that you are setting the range to. I am pretty
sure that setting the range in this manner requires the range to be set
from the activesheet.

Sub junk()
Dim xyz As Workbook
Dim x As Worksheet
Dim y As Worksheet
Dim z As Worksheet
Dim r As Range

'assuming abc is a workbook
Dim abc As Workbook
Set abc = Workbooks("book1")

Set xyz = ActiveWorkbook
' abc.Activate

Set x = xyz.Worksheets("X")
Set y = xyz.Worksheets("Y")
Set z = xyz.Worksheets("Z")

xyz.Activate
y.Activate
Set r = y.Range(Cells(1, 1), Cells(3, 3))
r.Font.Bold = True
End Sub

Regards--Lonnie M.

Because RANGE() wants a text string as an argument, try:


Set r = y.Range(Cells(1, 1).Address, Cells(3, 3).Address)
--
Gary's Student


"johnhildreth@citynet.net" wrote:

> I have a question regarding refering to ranges. Below is some sample
> (and simple) code to illustrate my question. I prefer to use R1C1
> notation and the cells property. But I have to use the A1 notation
> when I set the range "r" in the code below. Why can't I replace that
> line with:
>
> Set r = y.Range(cells(1,1),cells(3,3))
>
> This line returns the " Method 'Range' or object '_Worksheet' failed "
> error.
>
> Thanks for the help,
> John
>
> Sub junk()
>
> Dim xyz As Workbook
> Dim x As Worksheet
> Dim y As Worksheet
> Dim z As Worksheet
> Dim r As Range
>
> Set xyz = ActiveWorkbook
> abc.Activate
> '
> Set x = xyz.Worksheets("X")
> Set y = xyz.Worksheets("Y")
> Set z = xyz.Worksheets("Z")
>
> Set r = y.Range("A1:C3")
> r.Font.Bold = True
>
> End Sub
>
>