Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array from a Range using Offset
I am trying to create an Array from a Range of data in a spreadsheet. I am
able to do this successfully when I explicitly define the range, but I am having touble doing it using a variable and the Offset command. The following code works fine: Sub RangeToArray() Dim MyArray as Variant MyArray = Sheets(1).Range("A1:B5") End Sub What I would like to do is something like this: Sub RangeToArray() Dim MyArray as Variant Dim NumRows as Long Dim NumCols as Long NumRows = 5 NumCols = 2 MyArray = Sheets(1).Range("A1:(A1(offset(NumRows,NumCols)")) End Sub I just can't seem to get the syntax correct. I sure would appreciate any help from the group. Frank Hayes |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array from a Range using Offset
MyArray = Sheets(10).Range("A1:" & Range("A1").Offset(numRows, _
numCols).Address) Alan Beban Frank & Pam Hayes wrote: I am trying to create an Array from a Range of data in a spreadsheet. I am able to do this successfully when I explicitly define the range, but I am having touble doing it using a variable and the Offset command. The following code works fine: Sub RangeToArray() Dim MyArray as Variant MyArray = Sheets(1).Range("A1:B5") End Sub What I would like to do is something like this: Sub RangeToArray() Dim MyArray as Variant Dim NumRows as Long Dim NumCols as Long NumRows = 5 NumCols = 2 MyArray = Sheets(1).Range("A1:(A1(offset(NumRows,NumCols)")) End Sub I just can't seem to get the syntax correct. I sure would appreciate any help from the group. Frank Hayes |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array from a Range using Offset
Alan ... that worked perfectly. Thanks for the help.
Frank "Alan Beban" wrote in message ... MyArray = Sheets(10).Range("A1:" & Range("A1").Offset(numRows, _ numCols).Address) Alan Beban Frank & Pam Hayes wrote: I am trying to create an Array from a Range of data in a spreadsheet. I am able to do this successfully when I explicitly define the range, but I am having touble doing it using a variable and the Offset command. The following code works fine: Sub RangeToArray() Dim MyArray as Variant MyArray = Sheets(1).Range("A1:B5") End Sub What I would like to do is something like this: Sub RangeToArray() Dim MyArray as Variant Dim NumRows as Long Dim NumCols as Long NumRows = 5 NumCols = 2 MyArray = Sheets(1).Range("A1:(A1(offset(NumRows,NumCols)")) End Sub I just can't seem to get the syntax correct. I sure would appreciate any help from the group. Frank Hayes |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Array from a Range using Offset
Another possibility:
Dim MyArray as Variant Dim NumRows as Long Dim NumCols as Long NumRows = 5 NumCols = 2 MyArray = Sheets(10).Range("A1").Resize(numRows, _ numCols)).Value -- Regards, Tom Ogilvy "Alan Beban" wrote in message ... MyArray = Sheets(10).Range("A1:" & Range("A1").Offset(numRows, _ numCols).Address) Alan Beban Frank & Pam Hayes wrote: I am trying to create an Array from a Range of data in a spreadsheet. I am able to do this successfully when I explicitly define the range, but I am having touble doing it using a variable and the Offset command. The following code works fine: Sub RangeToArray() Dim MyArray as Variant MyArray = Sheets(1).Range("A1:B5") End Sub What I would like to do is something like this: Sub RangeToArray() Dim MyArray as Variant Dim NumRows as Long Dim NumCols as Long NumRows = 5 NumCols = 2 MyArray = Sheets(1).Range("A1:(A1(offset(NumRows,NumCols)")) End Sub I just can't seem to get the syntax correct. I sure would appreciate any help from the group. Frank Hayes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
Array Offset Function | Excel Worksheet Functions | |||
creating columns with data from offset rows | Excel Discussion (Misc queries) | |||
using offset with array formulas | Excel Worksheet Functions | |||
OFFSET and array formulae | Excel Discussion (Misc queries) |