Using Cells Method to define Ranges

[johnhildreth]

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

 

[Lonnie M.]

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.

 

[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.

 

[Guest]

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


Set r = y.Range(Cells(1, 1).Address, Cells(3, 3).Address)