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?

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?

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

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

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