setting range names

B

br549

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

p45cal

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

CoRrRan

br549 said:
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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top