A worksheet contains 10000 rows containing each 10 groups of (9 column
of values PLUS 1 empty column).
I want to define a range for each group and load them into an (integer
matrix. After performing some calculations and changing some values o
the a matrix I would like to write the array back into the range.

Something like:
*********
dim rngData as object
dim arrValues(10000,9) as integer

with worksheets("Sheet-1")
set rngdata = range(.cells(1,1),.cells(10000,9))
arrValues = rngData.getvalues() 'a call like that but I don
know the right one
for i=1 to 10000
for j = 1 to 9
arrValues(i,j) = arrvalues(i,j) *rnd() '/// just SOM
calculation
next j
next i
rngdata.values = arrvalues ' a call like that but I don t know th
right one
end with
*********

How can I read the values into the matrix and how can I store th
values onto the range EFFICIENTLY

--
JackRn
-----------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...fo&userid=3717
View this thread: http://www.excelforum.com/showthread.php?threadid=56888

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)
matrix. After performing some calculations and changing some values of
the a matrix I would like to write the array back into the range.

Something like:
*********
dim rngData as object
dim arrValues(10000,9) as integer

with worksheets("Sheet-1")
set rngdata = range(.cells(1,1),.cells(10000,9))
arrValues = rngData.getvalues() 'a call like that but I don t
know the right one
for i=1 to 10000
for j = 1 to 9
arrValues(i,j) = arrvalues(i,j) *rnd() '/// just SOME
calculation
next j
next i
rngdata.values = arrvalues ' a call like that but I don t know the
right one
end with
*********

How can I read the values into the matrix and how can I store the
values onto the range EFFICIENTLY?


--
JackRnl
Hi Jack,

Dim rngData as Range
Dim vaValues as Variant
With Worksheets("Sheet1")
Set rngdata = range(.cells(1,1),.cells(10000,9))
End With
vaValues = rngData
For i = 1 to 10000 'or 1 To Ubound(vaValues,1)
For j = 1 to 9 'or 1 To Ubound(vaValues,2)
vaValues(i,j) = vaValues(i,j) *rnd() '/// just SOME
calculation
next j
next i
rngData.Value = vaValues

Ken Johnson

hi Ken,

Thanks, the code does work but I still have a question about it.

I HAVE to use
Dim vaValues as Variant
instead of
Dim vaValues as Integer
or
Dim vaValues(10000,9) as integer
I see

But I can ReDim it later I found out and the code I made
*****
Sub Test()
Const NumRows = 10000
Const NumColumns = 9
Const NumSets = 10
Dim arrValues 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 = rngSet.Value
ReDim arrValues(NumRows, NumColumns) As Integer
For i = 1 To NumRows
For j = 1 To NumColumns
arrValues(i, j) = arrValues(i, j) * 2 'something
Next j
Next i
rngSet.Value = arrValues
rngSet.Columns.AutoFit
ColOffset = ColOffset + NumColumns + 1
Next NumSet
End With
End Sub
*****
does exactly what I want.

My questions is, as I know variables (and arrays) of type Variant use
much more space compared to variables of other types (like Integer),
does it mean my memory consumption will be much higher BEFORE and even
AFTER the ReDim?
This could be a problem for me as the code (also) has to run on PCs
with just a little memory. Of course I know I have to test it anyway
and maybe use a loop to handle all 10000 rows in chunks of let's say
1000 at a time.


--
JackRnl
------------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
View this thread: http://www.excelforum.com/showthread...hreadid=568889

If you set the dimensions of the array in the Dim statement, you cannot
change them after.
However, if Dim a dynamic array, you can. e.g.
Dim IntArray() as Integer
Redim IntArray(1 To 1000, 1 to 9)
'do something with IntArray
Redim IntArray(1 To 3)

NickHK

"JackRnl" wrote in
message ...

hi Ken,

Thanks, the code does work but I still have a question about it.

I HAVE to use
Dim vaValues as Variant
instead of
Dim vaValues as Integer
or
Dim vaValues(10000,9) as integer
I see

But I can ReDim it later I found out and the code I made
*****
Sub Test()
Const NumRows = 10000
Const NumColumns = 9
Const NumSets = 10
Dim arrValues 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 = rngSet.Value
ReDim arrValues(NumRows, NumColumns) As Integer
For i = 1 To NumRows
For j = 1 To NumColumns
arrValues(i, j) = arrValues(i, j) * 2 'something
Next j
Next i
rngSet.Value = arrValues
rngSet.Columns.AutoFit
ColOffset = ColOffset + NumColumns + 1
Next NumSet
End With
End Sub
*****
does exactly what I want.

My questions is, as I know variables (and arrays) of type Variant use
much more space compared to variables of other types (like Integer),
does it mean my memory consumption will be much higher BEFORE and even
AFTER the ReDim?
This could be a problem for me as the code (also) has to run on PCs
with just a little memory. Of course I know I have to test it anyway
and maybe use a loop to handle all 10000 rows in chunks of let's say
1000 at a time.


--
JackRnl
------------------------------------------------------------------------
JackRnl's Profile:
http://www.excelforum.com/member.php...o&userid=37172
View this thread: http://www.excelforum.com/showthread...hreadid=568889

Hi Jack,

If memory is an issue then I guess you'll have to use...

Sub Test()
Const NumRows = 10000
Const NumColumns = 9
Const NumSets = 10
Dim arrValues(1 To NumRows, 1 To NumColumns) As Integer
Dim rngSet As Range
Dim NumSet As Integer
Dim i As Integer
Dim j As Integer
Dim ColOffset As Integer
With Worksheets("Test")
ColOffset = 0
For NumSet = 1 To NumSets
Set rngSet = Range(.Cells(1, ColOffset + 1), _
..Cells(NumRows, ColOffset + NumColumns))
For i = 1 To NumRows
For j = 1 To NumColumns
arrValues(i, j) = rngSet.Cells(i, j).Value
Next j
Next i
For i = 1 To NumRows
For j = 1 To NumColumns
arrValues(i, j) = arrValues(i, j) * 2 'something
Next j
Next i
rngSet.Value = arrValues
rngSet.Columns.AutoFit
ColOffset = ColOffset + NumColumns + 1
Next NumSet
End With
End Sub


Which I'm reasonable sure will be nowhere near as fast as the first
method.
Just hope that memory is not an issue.

BTW you probably didn't mean to but you had NumSet, j and i all Dim as
Variant.

Ken Johnson

Indeed it makes a *big* differences using just *one* call top transfer
all the data instead of looping through all the cells.
In my app it means I can reduce runtime from 200 seconds to just 10
seconds.
But I have a *big* problem right now.


Sub Test()
Dim rngResults As Range
Set rngResults = Evaluate("Results")
NumLines = rngResults.Rows.Count
Dim arrResults As Variant
arrResults = rngResults
ReDim arrResults(1 To NumLines)

Const bWorks = False

For i = 1 To NumLines
arrResults(i) = i * 4
Next i

With Worksheets("Results")
..Unprotect
If bWorks = True Then
For i = 1 To NumLines
rngResults(i) = arrResults(i)
Next i
Else
rngResults = arrResults
End If
..Protect
End With
End Sub

if I execute the code above (with bWorks=False) *all* cells of
rngResults will have the value 4 , but if I change bWorks = true the
code above shows different values (the right ones) for each of the
cells or rngResults.
Obviously it is using the *first* value of arrResults for *every* cell
of bWorks=False. What must I do to transfer the array arrResults into
rngResults in *one* action

BTW, thanks for all the help


--
JackRnl
------------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...o&userid=37172
View this thread: http://www.excelforum.com/showthread...hreadid=568889

Although you didn't disclose it in your posting (which wasted a
considerable amount of my time), the range named "Results" is apparently
a single column of cells. If that is the case, the difficulty is that
when you execute rngResults = arrResults you are trying to set a column
of cell values to a set of array "row" values. I.e., the range is
vertical, the array is "horizontal".

rngResults = Application.Transpose(arrResults) will work if you're using
a version of Excel later than xl2000 or if arrResults is smaller than
5462 elements.

Alan Beban

JackRnl wrote:
Indeed it makes a *big* differences using just *one* call top transfer
all the data instead of looping through all the cells.
In my app it means I can reduce runtime from 200 seconds to just 10
seconds.
But I have a *big* problem right now.


Sub Test()
Dim rngResults As Range
Set rngResults = Evaluate("Results")
NumLines = rngResults.Rows.Count
Dim arrResults As Variant
arrResults = rngResults
ReDim arrResults(1 To NumLines)

Const bWorks = False

For i = 1 To NumLines
arrResults(i) = i * 4
Next i

With Worksheets("Results")
.Unprotect
If bWorks = True Then
For i = 1 To NumLines
rngResults(i) = arrResults(i)
Next i
Else
rngResults = arrResults
End If
.Protect
End With
End Sub

if I execute the code above (with bWorks=False) *all* cells of
rngResults will have the value 4 , but if I change bWorks = true the
code above shows different values (the right ones) for each of the
cells or rngResults.
Obviously it is using the *first* value of arrResults for *every* cell
of bWorks=False. What must I do to transfer the array arrResults into
rngResults in *one* action

BTW, thanks for all the help

Hi Jack,

Alan has pointed out the problem.

When using the variant array, what you were expecting to be an array
with many rows and one column was in fact the opposite, many columns
and one row. So, you ended up with with i repetitions of the first
element of that variant array. The solution is to get the wide array
onto the sheet as a single column of many rows using the Transpose
WorksheetFunction. Alan's reply shows the solution and a problem you
will have if you're using xl2000 or earlier.

Ken Johnson

The OP indeed was "how to put a *matrix* (multiple array) to a range an
the info supplied by Ken did solve that.
But I also had a problem with an array consisting of a single column.
It was strange to me it didn't work transferring the data for a singl
column but *did* work with a multiple columns. I just didn't see th
fundamental difference so I didn't mention "single column" (allthoug
the code I supplied later
For i = 1 To NumLines
arrResults(i) = i * 4
Next i
did indicate I was talking about a single column at that time)
Sorry Alan for not being that clear and wasting your time.

Thanks to the info supplied I searched the internet for the 546
limitation and found some remarkable things.

First I found this very intersting article http://tinyurl.com/qt9xt
and now I will be using
Function TransposeArray(ByRef arr)
'// Application.Transpose has a limit of 5461 items (In case NO
XL2002 and the later version).
'// Causing "Run-time Error 13 Type mismatch"
'// But Excel WILL accept a multi dimensional array of the righ
size, so convert it
'// http://support.microsoft.com/kb/177991/
ReDim buf(LBound(arr) To UBound(arr), LBound(arr) To LBound(arr))
For i = LBound(buf) To UBound(buf)
buf(i, LBound(buf)) = arr(i)
Next i
TransposeArray = buf '// A multi column array will be returned
End Function

and *MyRange = TransposeArray(MyArray)* to get values into the rang
for a singlecolumn range and I think I will expand the function to tak
care of multicolumn arrays as well in order to use the *same* code fo
*any* filling of a range with values from an array and forget about
rng = multi_arr 'for range with multiple columns
rng = application.transpose 'for range with single column and "few
elements
rng = TransposeArray 'for range with single column an
"many" elements

I als saw this code
Sub Does_Work_Xl2000()
Dim rng As Variant, arr As Variant
Dim Lst As Long
Lst = 10000
rng = Evaluate("transpose(row(1:" & Lst & ")/row(1:" & Lst & "))")
arr = rng
Debug.Print UBound(arr) & ": " & arr(UBound(arr))
End Sub

but I didn't have a problem assigning values from a large single colum
range into an array and I didn't see the need for something similar (
TransposeRange) . But maybe I made a mistake.

Anyway it seems as Microsoft did solve the problem for Excel version
after XL2000 at the place where it should be : in *its* code
So I would like to know how can test for easily and fast for wha
version I'm using (will Application.Version be needed -- returning
*string* instead of a "value* so testing like *if Application.versio
"9.0" then* will fail for later versions) ro change TransposeArra
accordingly.
I also would like to know if I need to use the following code t
findout if an array has one or several dimension

bJustone=true
on error goto JustOne
SecondDimension=ubound(rng,2)
bJustOne=False
JustOne:

One further remark, I use *ByRef* in my TransposeArray function instea
of *ByVal* I've seen in other code, would you please tell if I *need* t
use ByVal


Thanks for all comments, they helped a lot and made my code run correc
and faster (from 200 seconds down to 10

--
JackRn
-----------------------------------------------------------------------
JackRnl's Profile: http://www.excelforum.com/member.php...fo&userid=3717
View this thread: http://www.excelforum.com/showthread.php?threadid=56888