Problem using Range objects
 

Hi,

in a subroutine I'm writing, I'm trying to create a reference to the
first 5 cells of the second column of a Range object, which is *not*
on the active sheet. I have two questions:

1. How would you do that?
2. I tried writing two different subroutines which should do the same
job, but one doesn't work and I don't know why .
-------------------------------------------------------------------------------------
Sub test()
Dim MyRange As Range
Dim sht As Worksheet

' Set the work sheet
Sheets("Sheet1").Activate

' Set range
With sht
Set MyRange = .Range(.Cells(3, 3), .Cells(57, 6))
End With

Call ValidProcedure(MyRange)
'Call InvalidProcedure(MyRange)

End Sub
----------------------------------------------------------------------------------------------------------
Sub ValidProcedure(MyRange As Range)
Dim MySubRange As Range

' MyRange is a range on sheet "Sheet1"

Sheets("Sheet2").Activate

Set MySubRange = Range(MyRange.Cells(1, 2), MyRange.Cells(5, 2))

End Sub
---------------------------------------------------------------------------------------------------------
Sub InvalidProcedure(MyRange As Range)
Dim MySubRange As Range

' MyRange is a range on sheet "Sheet1"

Sheets("Sheet2").Activate

With MyRange
Set MySubRange = .Range(.Cells(1, 2), Cells(5, 2))
End With

End Sub
--------------------------------------------------------------------------------------------------------

In the second subroutine, MySubRange points to a range which is not
the one desired. Can you help me understanding what's happening?
Thanks,

Best Regards

Sergio

Problem using Range objects
 

Hi
In test() you have not defined what sht is, only declared it as a
sheet object variable.
You can do:
Set sht = Activesheet
or
Set sht = Sheets("Sheet1")
or
Set sht=worksheets("mysheetname")
or
or
Set sht = Activeworkbook.worksheets("mysheetname")
or
Set sht = Workbooks("myworkbook").worksheets("mysheetname") 'if
myworkbook is open

then use sht and test() should work. I would tend to fully qualify the
sheet object as in the last two examples.
In your two called subs you activate Sheet1 in both. I would pull it
into the main sub at the end. You never need to activate a sheet
unless you want it to be the one displayed when the sub has finished.
If you fully qualify sheet names you will rarely need to activate a
sheet & it tends to produce confusing code which might do inexplicable
things..
regards
Paul

On Dec 3, 1:47*pm, deltaquattro wrote:
Hi,

in a subroutine I'm writing, I'm trying to create a reference to the
first 5 cells of the second column of a Range object, which is *not*
on the active sheet. I have two questions:

1. How would you do that?
2. I tried writing two different subroutines which should do the same
job, but one doesn't work and I don't know why .
-------------------------------------------------------------------------------------
Sub test()
Dim MyRange As Range
Dim sht As Worksheet

' Set the work sheet
Sheets("Sheet1").Activate

' Set range
With sht
* * Set MyRange = .Range(.Cells(3, 3), .Cells(57, 6))
End With

Call ValidProcedure(MyRange)
'Call InvalidProcedure(MyRange)

End Sub
----------------------------------------------------------------------------------------------------------
Sub ValidProcedure(MyRange As Range)
Dim MySubRange As Range

' MyRange is a range on sheet "Sheet1"

Sheets("Sheet2").Activate

Set MySubRange = Range(MyRange.Cells(1, 2), MyRange.Cells(5, 2))

End Sub
---------------------------------------------------------------------------------------------------------
Sub InvalidProcedure(MyRange As Range)
Dim MySubRange As Range

' MyRange is a range on sheet "Sheet1"

Sheets("Sheet2").Activate

With MyRange
* * Set MySubRange = .Range(.Cells(1, 2), Cells(5, 2))
End With

End Sub
--------------------------------------------------------------------------------------------------------

In the second subroutine, MySubRange points to a range which is not
the one desired. Can you help me understanding what's happening?
Thanks,

Best Regards

Sergio