Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Range not working | Excel Worksheet Functions | |||
Working with the range object | Excel Discussion (Misc queries) | |||
Working with a range | Excel Discussion (Misc queries) | |||
setting a range not working, please help | Excel Programming | |||
Range selection not working | Excel Programming |