Working with range
 

Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80 rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the number
of elements coming from the columns I get an error 9 "Subscript out of range".

What is wrong in my code ?

Thanks
François

Working with range
 

Francois,

You are using the UBound function incorrectly:

ThisRange = Range("A2:N81")
MsgBox UBound(ThisRange, 1) 'returns 80
MsgBox UBound(ThisRange, 2) 'returns 14

ThisRange(1) is the first cell, cell A2.

ThisRange(1) is shorthand for ThisRange.Item(1)

HTH,
Bernie
MS Excel MVP


"François" wrote in message
...
Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80 rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the
number
of elements coming from the columns I get an error 9 "Subscript out of
range".

What is wrong in my code ?

Thanks
François

Working with range
 

demo't from the immediate window:

ThisRange = Range("A2:N81").Value
? lbound(thisrange,1), ubound(thisrange,1)
1 80
? lbound(thisrange,2), ubound(thisrange,2)
1 14



--
Regards,
Tom Ogilvy

"François" wrote in message
...
Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80 rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the
number
of elements coming from the columns I get an error 9 "Subscript out of
range".

What is wrong in my code ?

Thanks
François

Working with range
 

Think you shifted from talking about Arrays to talking about ranges.

ThisRange(1) would raise an error since ThisRange is a two dimensional
array. And an array itself doesn't have any properties such as Item.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Francois,

You are using the UBound function incorrectly:

ThisRange = Range("A2:N81")
MsgBox UBound(ThisRange, 1) 'returns 80
MsgBox UBound(ThisRange, 2) 'returns 14

ThisRange(1) is the first cell, cell A2.

ThisRange(1) is shorthand for ThisRange.Item(1)

HTH,
Bernie
MS Excel MVP


"François" wrote in message
...
Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80 rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the
number
of elements coming from the columns I get an error 9 "Subscript out of
range".

What is wrong in my code ?

Thanks
François

Working with range
 

Thanks Tom.
The purpose of using a range rather than using a logic like
activecell.(offset(0,1)) etc was to save some process time. Purpose is to
retrieve the colors of each cell of a range ("a2:n81") stored in one
worksheet and to copy some cells to another worksheet.
But I can not do :
Dim ThisRange as Range
ThisRange = Range("A2:N81") cause this is giving a runtime error.
Why ?

Thanks.


"Tom Ogilvy" wrote:

demo't from the immediate window:

ThisRange = Range("A2:N81").Value
? lbound(thisrange,1), ubound(thisrange,1)
1 80
? lbound(thisrange,2), ubound(thisrange,2)
1 14



--
Regards,
Tom Ogilvy

"François" wrote in message
...
Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80 rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the
number
of elements coming from the columns I get an error 9 "Subscript out of
range".

What is wrong in my code ?

Thanks
François

Working with range
 

Exactly what is it that is "giving . . . a runtime error"?

In any event, try Set ThisRange = Range("A2:N81")

Alan Beban

François wrote:
Thanks Tom.
The purpose of using a range rather than using a logic like
activecell.(offset(0,1)) etc was to save some process time. Purpose is to
retrieve the colors of each cell of a range ("a2:n81") stored in one
worksheet and to copy some cells to another worksheet.
But I can not do :
Dim ThisRange as Range
ThisRange = Range("A2:N81") cause this is giving a runtime error.
Why ?

Thanks.


"Tom Ogilvy" wrote:


demo't from the immediate window:

ThisRange = Range("A2:N81").Value
? lbound(thisrange,1), ubound(thisrange,1)
1 80
? lbound(thisrange,2), ubound(thisrange,2)
1 14



--
Regards,
Tom Ogilvy

"François" wrote in message
...

Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80 rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the

number

of elements coming from the columns I get an error 9 "Subscript out of

range".

What is wrong in my code ?

Thanks
François

Working with range
 

You introduced the term Array and the code you posted did result in an array
being stored in the variant variable Thisrange and you use ubound which is a
function that operates on arrays. So my answer was for an array question.

If you are trying to retrieve colors from cells, then you don't need an
array. Alan showed you the correction

Dim ThisRange as Range
set ThisRange = Range("A2:N81")
msgbox thisrange.Address

if you want the number of rows and columns

msgbox thisrange.rows.count
msgbox thisrange.columns.count

if you want to refer to B3

msgbox thisrange.offset(1,1).Address



--
Regards,
Tom Ogilvy

"François" wrote in message
...
Thanks Tom.
The purpose of using a range rather than using a logic like
activecell.(offset(0,1)) etc was to save some process time. Purpose is to
retrieve the colors of each cell of a range ("a2:n81") stored in one
worksheet and to copy some cells to another worksheet.
But I can not do :
Dim ThisRange as Range
ThisRange = Range("A2:N81") cause this is giving a runtime error.
Why ?

Thanks.


"Tom Ogilvy" wrote:

demo't from the immediate window:

ThisRange = Range("A2:N81").Value
? lbound(thisrange,1), ubound(thisrange,1)
1 80
? lbound(thisrange,2), ubound(thisrange,2)
1 14



--
Regards,
Tom Ogilvy

"François" wrote in message
...
Hello

I have some problems dealing with the arrays.
Here the code

ThisRange = Range("A2:N81")
It supposed to be a array of 2 dimensions of type variant
So If I do uBound(ThisRange) I get 80 which seems to be right (80
rows).
But if I asked uBound(ThisRange(1)) which is supposed to give me the
number
of elements coming from the columns I get an error 9 "Subscript out of
range".

What is wrong in my code ?

Thanks
François