Problem with Worksheets v Ranges 'Again'

[Ade]

I seem to have a lot of difficulty using / selecting ranges that i have
defined on different Worksheets. I have read that you need to explictly
define the range which I often try to do - with usually poor results as I
always get the syntax wrong.
My current problem occurs in the following code module :-

Dim Range1 As Range
Dim Range2 As Range
Dim StartRange1 As Integer
Dim EndRange1 As Integer
Dim StartRange2 As Integer
Dim EndRange2 As Integer
Const RangeSize As Integer = 55


Sub Init()
StartRange1 = 3
EndRange1 = 57
StartRange2 = 2
EndRange2 = 56
Set Range1 = Range("A" & StartRange1 & ":" & "A" & EndRange1)
Set Range2 = Worksheets("ColumnSplits").Range("A" & StartRange2 & ":" &
"A" & EndRange2)
Range1.Select
With Worksheets("ColumnSplits")
.Range2.Select
End With

End Sub

Error : 'Object does not support this property or method' at the line near
the bottom '.Range2.Select'.

Anyone who can suugest the correct code to Select Range2? Also is there any
usefull methods for figuring out which code syntax you have to use when
working with ranges on different Worksheets as it never seems obvious to me,
for example in my code I can not see why the line '.Range2.Select' will not
work, it seems like the obvious solution to me.

Cheers

 

[Bob Phillips]

Range2 is a variable, a range object variable, not a property of
Worksheets("ColumnSplits"), so don't qualify it as such, i.e.

With Worksheets("ColumnSplits")
.Range2.Select
End With

should just be

Range2.Select

BTW, you can define it a little simpler

Set Range1 = Range("A" & StartRange1 & ":" & "A" & EndRange1)

becomes

Set Range1 = Range("A" & StartRange1 & ":A" & EndRange1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[greyflame]

Range2 is a variable in your code and is not a member of the WorkSheets
object, which is how your code is attempting to access it.

You should use it just like you do with your Range1 variable.

ie
Range2.select

 

[Dave Peterson]

Since Range2 is already a range, it comes with all the properties that range
objects have. Including its parent. (The parent of the range is the worksheet
that owns it.)

So you don't respecify the worksheet when you refer to Range2 (Range2 already
knows where it belongs).

This won't work:

With Worksheets("ColumnSplits")
.Range2.Select
End With

But this will

Worksheets("ColumnSplits").select
range2.select

(You have to be on the worksheet to select a range.)

With Worksheets("ColumnSplits")
.Select 'still needed
.range("a1:b99").select
End With

..range property is different than your Range2 variable.

 

[JLatham]

When you 'Set' a range variable it refers to the entire area you referenced
on to right side of the = symbol. So Range2 already refers to A2:A56 on
sheet ColumnSplits.
When you say
With Worksheets("ColumnSplits")
.Range2.Select
End With
you are actually saying
Worksheets("ColumnSplits").Range(Worksheets("ColumnSplits").Range("A2:A56").Select
which confuses Excel since there is no worksheet ColumnSplits within
worksheet ColumnSplits.

Also, Excel doesn't like trying to select a range unless the sheet it is on
is already selected/active. Your code would probably work like this:
Sub Init()
StartRange1 = 3
EndRange1 = 57
StartRange2 = 2
EndRange2 = 56
Set Range1 = Range("A" & StartRange1 & ":" & "A" & EndRange1)
Set Range2 = Worksheets("ColumnSplits").Range("A" & StartRange2 & ":" &
"A" & EndRange2)
Range1.Select
Worksheets("ColumnSplits").Activate ' or .Select
Range2.Select
End Sub

When you want to set a range variable (as Range1) to a range on the current
worksheet, use the same format you did in your code, and when you need it to
reference a range in another workbook/worksheet do as you did with Range2
(with specified workbook also if you need to reference worksheet & range in a
different, open workbook). Hope that helps some.

 

[Vergel Adriano]

Hi,

Range2 is not a property of the Worksheet object. Rather, it's a variable
that you defined, just like Range1. So, you use it also just like how you
used Range1.

Range2.Select

 

[Ade]

Thank you all for your help, I think I have confused 'selecting' the range on
a different worksheet with 'working with' a range on a different worksheet as
I do not want to actually have to make the VBA code change between worksheets.