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