setting range names

[Guest]

Why doesn't this code work?:

Sub test1()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range

Sheets("Sheet2").Activate
Set ws2 = Worksheets("Sheet2")
Set ws1 = Worksheets("Sheet1")
Set rng1 = ws1.Range(Cells(3, 1), Cells(7, 1))
Set rng2 = ws2.Range(Cells(5, 5), Cells(5, 5))

When it gets to the "Set rng2 ..." line it give a "Runtime error '1004' ...."
It seems strange that it works for the "Set rng1" line without problem.

Also, I can't seem to find a good reference that will help me learn the
idiosyncracies of setting/naming ranges, worksheets, and workbooks.

 

[Guest]

try:
Set rng1 = Range(ws1.Cells(3, 1), ws1.Cells(7, 1))
Set rng2 = Range(ws2.Cells(5, 5), ws2.Cells(5, 5))

 

[CoRrRan]

Why doesn't this code work?:

Sub test1()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range

Sheets("Sheet2").Activate
Set ws2 = Worksheets("Sheet2")
Set ws1 = Worksheets("Sheet1")
Set rng1 = ws1.Range(Cells(3, 1), Cells(7, 1))
Set rng2 = ws2.Range(Cells(5, 5), Cells(5, 5))

When it gets to the "Set rng2 ..." line it give a "Runtime error '1004' ...."
It seems strange that it works for the "Set rng1" line without problem.

Also, I can't seem to find a good reference that will help me learn the
idiosyncracies of setting/naming ranges, worksheets, and workbooks.

Try this:

*********************
Sub test()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range

Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")

Set rng1 = ws1.Cells(3, 1).Resize(5, 1)
Set rng2 = ws2.Cells(5, 5).Resize(1, 1)

End Sub
*********************

As for your problem, if you would have done something like this:

Set rng1 = ws1.Range(ws1.Cells(3, 1), ws1.Cells(7, 1))

It would have worked.

CoRrRan

 

[RB Smissaert]

You will need this:

Dim ws2 As Worksheet
Dim rng2 As Range

Set ws2 = Worksheets(2)

With ws2
Set rng2 = .Range(.Cells(5, 5), .Cells(5, 5))
End With

Note the dots before Cells.


RBS