I have an array - RangeArray = Array("Range1", "Range2","Range3") and I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock


*** Sent via Developersdex http://www.developersdex.com ***

Ken,

This should get you started

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim RangeArray
Dim m As Long

Set Range1 = Worksheets(1).Range("A1:D5")
Set Range2 = Worksheets(1).Range("A11:D15")
Set Range3 = Worksheets(1).Range("A21:D25")
RangeArray = Array(Range1, Range2, Range3)
m = 1
MsgBox RangeArray(m).Address


--

HTH

RP
(remove nothere from the email address if mailing direct)


"kenrock" wrote in message
...

I have an array - RangeArray = Array("Range1", "Range2","Range3") and I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock


*** Sent via Developersdex http://www.developersdex.com ***

How about:

Set myRange = range(RangeArray(m))

or if they're all on worksheets(1):

set myrange = worksheets(1).range(rangearray(m))




kenrock wrote:

I have an array - RangeArray = Array("Range1", "Range2","Range3") and I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

May I add a small point?.
Baring in mind that we will be referring to Range2 when we use m=1 (cause
for three ranges m=0, 1, and 2)..:)
J_J

"Bob Phillips" wrote in message
...
Ken,

This should get you started

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim RangeArray
Dim m As Long

Set Range1 = Worksheets(1).Range("A1:D5")
Set Range2 = Worksheets(1).Range("A11:D15")
Set Range3 = Worksheets(1).Range("A21:D25")
RangeArray = Array(Range1, Range2, Range3)
m = 1
MsgBox RangeArray(m).Address


--

HTH

RP
(remove nothere from the email address if mailing direct)


"kenrock" wrote in message
...

I have an array - RangeArray = Array("Range1", "Range2","Range3") and I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock


*** Sent via Developersdex http://www.developersdex.com ***

Of course, it is well made :-))

Unfortunately, it is not absolute. Try it again, but add this line to the
start of the module and see what you get

Option Base 1

Bob


"J_J" wrote in message
...
May I add a small point?.
Baring in mind that we will be referring to Range2 when we use m=1 (cause
for three ranges m=0, 1, and 2)..:)
J_J

"Bob Phillips" wrote in message
...
Ken,

This should get you started

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim RangeArray
Dim m As Long

Set Range1 = Worksheets(1).Range("A1:D5")
Set Range2 = Worksheets(1).Range("A11:D15")
Set Range3 = Worksheets(1).Range("A21:D25")
RangeArray = Array(Range1, Range2, Range3)
m = 1
MsgBox RangeArray(m).Address


--

HTH

RP
(remove nothere from the email address if mailing direct)


"kenrock" wrote in message
...

I have an array - RangeArray = Array("Range1", "Range2","Range3") and
I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is
an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock


*** Sent via Developersdex http://www.developersdex.com ***

kenrock wrote:
I have an array - RangeArray = Array("Range1", "Range2","Range3") and I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock


*** Sent via Developersdex http://www.developersdex.com ***
Gentlemen,
Many thanks for your prompt and knowledgeable responses to my query.
I now have the interesting task of trying out the solutions and at the
same time, improving my understanding of Excel spreadsheets.
Regards, Ken Rock

Yep!. Now I get Range1 as expected.
You are correct as before Bob.
Cheers
J_J

"Bob Phillips" wrote in message
...
Of course, it is well made :-))

Unfortunately, it is not absolute. Try it again, but add this line to the
start of the module and see what you get

Option Base 1

Bob


"J_J" wrote in message
...
May I add a small point?.
Baring in mind that we will be referring to Range2 when we use m=1 (cause
for three ranges m=0, 1, and 2)..:)
J_J

"Bob Phillips" wrote in message
...
Ken,

This should get you started

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim RangeArray
Dim m As Long

Set Range1 = Worksheets(1).Range("A1:D5")
Set Range2 = Worksheets(1).Range("A11:D15")
Set Range3 = Worksheets(1).Range("A21:D25")
RangeArray = Array(Range1, Range2, Range3)
m = 1
MsgBox RangeArray(m).Address


--

HTH

RP
(remove nothere from the email address if mailing direct)


"kenrock" wrote in message
...

I have an array - RangeArray = Array("Range1", "Range2","Range3") and
I
define, elsewhere, Range1 as Worksheets(1).Range("A1:D5"), Range2 as
Worksheets(1).Range("J20:M35") etc...
I want to write the code such that myRange = RangeArray(m) where m is
an
integer. Thus by choosing a value for m, I can define a particular
range. I've tried several ways but I am not having much luck. Can
anyone please help?
Regards, Ken Rock


*** Sent via Developersdex http://www.developersdex.com ***