Problem using Range objects

[deltaquattro]

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

 

[Paul Robinson]

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