Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring array to range
A worksheet contains 10000 rows containing each 10 groups of (9 columns
of values PLUS 1 empty column). I want to define a range for each group and load them into an (integer) array. After performing some calculations and changing some values of the array I would like to write the array back into the range. How can I read the values into the array and how can I store the values onto the range efficiently? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring array to range
One example. Since the array is one dimension, must use Transpose for a
column of data (not required when referencing a row of data or 2D reference). Sub test() Dim rngData As Range Dim arrData As Variant Dim i As Long Set rngData = Range("A1:A10") arrData = Application.Transpose(rngData.Value) For i = LBound(arrData) To UBound(arrData) If IsNumeric(arrData(i)) Then _ arrData(i) = arrData(i) * 2 Next i rngData.Value = Application.Transpose(arrData) End Sub "JackRnl" wrote: A worksheet contains 10000 rows containing each 10 groups of (9 columns of values PLUS 1 empty column). I want to define a range for each group and load them into an (integer) array. After performing some calculations and changing some values of the array I would like to write the array back into the range. How can I read the values into the array and how can I store the values onto the range efficiently? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring array to range
JMB wrote:
One example. Since the array is one dimension, must use Transpose for a column of data (not required when referencing a row of data or 2D reference). Sub test() Dim rngData As Range Dim arrData As Variant Dim i As Long Set rngData = Range("A1:A10") arrData = Application.Transpose(rngData.Value) For i = LBound(arrData) To UBound(arrData) If IsNumeric(arrData(i)) Then _ arrData(i) = arrData(i) * 2 Next i rngData.Value = Application.Transpose(arrData) End Sub "JackRnl" wrote: A worksheet contains 10000 rows containing each 10 groups of (9 columns of values PLUS 1 empty column). I want to define a range for each group and load them into an (integer) array. After performing some calculations and changing some values of the array I would like to write the array back into the range. How can I read the values into the array and how can I store the values onto the range efficiently? I wrote following code ********* Sub Test() Const NumRows = 10000 Const NumColumns = 9 Const NumSets = 10 Dim arrValues(NumRows, NumColumns) As Variant Dim rngSet As Range Dim NumSet, i, j, ColOffset as Integer With Worksheets("Test") ColOffset = 0 For NumSet = 1 to NumSets Set rngSet = Range(.Cells(1, ColOffset + 1), .Cells(NumRows, ColOffset + NumColumns)) arrValues = Application.Transpose(rngSet.Values) '*wrong* For i = 1 To NumRows For j = 1 To NumColumns arrValues(i, j) = arrValues(i,j) * 2 'do something Next j Next i rngSet = arrValues rngSet.Columns.AutoFit ColOffset = ColOffset + NumColumns + 1 Next NumSet End With End Sub ********* Apart from the statement marked *wrong* the code does work and quite fast indeed, but still the question how I can retrieve the values from the range rngSet and put them into arrValues at the line marked *wrong* as that Transpose statement doesn't work in Excel 2000 using VBA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring array to range
Transpose is a WorksheetFunction. So
Application.WorksheetFunction.Transpose(..etc NickHK "JackRnl" wrote in message . nl... JMB wrote: One example. Since the array is one dimension, must use Transpose for a column of data (not required when referencing a row of data or 2D reference). Sub test() Dim rngData As Range Dim arrData As Variant Dim i As Long Set rngData = Range("A1:A10") arrData = Application.Transpose(rngData.Value) For i = LBound(arrData) To UBound(arrData) If IsNumeric(arrData(i)) Then _ arrData(i) = arrData(i) * 2 Next i rngData.Value = Application.Transpose(arrData) End Sub "JackRnl" wrote: A worksheet contains 10000 rows containing each 10 groups of (9 columns of values PLUS 1 empty column). I want to define a range for each group and load them into an (integer) array. After performing some calculations and changing some values of the array I would like to write the array back into the range. How can I read the values into the array and how can I store the values onto the range efficiently? I wrote following code ********* Sub Test() Const NumRows = 10000 Const NumColumns = 9 Const NumSets = 10 Dim arrValues(NumRows, NumColumns) As Variant Dim rngSet As Range Dim NumSet, i, j, ColOffset as Integer With Worksheets("Test") ColOffset = 0 For NumSet = 1 to NumSets Set rngSet = Range(.Cells(1, ColOffset + 1), .Cells(NumRows, ColOffset + NumColumns)) arrValues = Application.Transpose(rngSet.Values) '*wrong* For i = 1 To NumRows For j = 1 To NumColumns arrValues(i, j) = arrValues(i,j) * 2 'do something Next j Next i rngSet = arrValues rngSet.Columns.AutoFit ColOffset = ColOffset + NumColumns + 1 Next NumSet End With End Sub ********* Apart from the statement marked *wrong* the code does work and quite fast indeed, but still the question how I can retrieve the values from the range rngSet and put them into arrValues at the line marked *wrong* as that Transpose statement doesn't work in Excel 2000 using VBA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring array to range
I've never run into problems w/omitting WorksheetFunction when using excel
native functions. I think the primary problem is Dim arrValues(NumRows, NumColumns) As Variant declared an array of variants instead of a variant that contains an array. My references indicate the variable must be dimmed as a variant (and, as I recall, have not been able to get it to work otherwise). Secondly, since the array is 2D, Transpose is not necessary. I thought the OP meant each group consisted of just one column, so I added that function to make it easier to reference the individual elements of the array. Last, rngSet.Values s/b rngSet.Value To the OP, try: Dim arrValues As Variant and arrValues = rngSet.Value "NickHK" wrote: Transpose is a WorksheetFunction. So Application.WorksheetFunction.Transpose(..etc NickHK "JackRnl" wrote in message . nl... JMB wrote: One example. Since the array is one dimension, must use Transpose for a column of data (not required when referencing a row of data or 2D reference). Sub test() Dim rngData As Range Dim arrData As Variant Dim i As Long Set rngData = Range("A1:A10") arrData = Application.Transpose(rngData.Value) For i = LBound(arrData) To UBound(arrData) If IsNumeric(arrData(i)) Then _ arrData(i) = arrData(i) * 2 Next i rngData.Value = Application.Transpose(arrData) End Sub "JackRnl" wrote: A worksheet contains 10000 rows containing each 10 groups of (9 columns of values PLUS 1 empty column). I want to define a range for each group and load them into an (integer) array. After performing some calculations and changing some values of the array I would like to write the array back into the range. How can I read the values into the array and how can I store the values onto the range efficiently? I wrote following code ********* Sub Test() Const NumRows = 10000 Const NumColumns = 9 Const NumSets = 10 Dim arrValues(NumRows, NumColumns) As Variant Dim rngSet As Range Dim NumSet, i, j, ColOffset as Integer With Worksheets("Test") ColOffset = 0 For NumSet = 1 to NumSets Set rngSet = Range(.Cells(1, ColOffset + 1), .Cells(NumRows, ColOffset + NumColumns)) arrValues = Application.Transpose(rngSet.Values) '*wrong* For i = 1 To NumRows For j = 1 To NumColumns arrValues(i, j) = arrValues(i,j) * 2 'do something Next j Next i rngSet = arrValues rngSet.Columns.AutoFit ColOffset = ColOffset + NumColumns + 1 Next NumSet End With End Sub ********* Apart from the statement marked *wrong* the code does work and quite fast indeed, but still the question how I can retrieve the values from the range rngSet and put them into arrValues at the line marked *wrong* as that Transpose statement doesn't work in Excel 2000 using VBA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix an array or range | Excel Discussion (Misc queries) | |||
Problem transferring array data onto worksheet using Resize | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Transferring part of a multi-dimensional array to a range in VBA | Excel Programming | |||
Array <--- Range | Excel Programming |