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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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
news:O%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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
>>
>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >