Transfer data from worksheet to array

[Guest]

i need to transfer data from excel worksheet to an array. one way to do is
read cell by cell. this works fine but is very time consuming. for example:

Sheet1.Activate
For CounterNumber1 = 1 To 10
For CounterNumber2 = 1 To 10
OriginalMatrix(CounterNumber1, CounterNumber2) =
Sheet1.Cells(CounterNumber1, CounterNumber2).Value
Next CounterNumber2
Next CounterNumber1

is there a way to tranfer the entire range a1 to j10 to the array at a time
so that computation becomes faster. the following code doesn't work:

Sheet1.Activate
OriginalMatrix = Sheet1.Range("a1:j10").value

any suggestions please?

tia

 

[Ken Puls]

So far as I am aware, you must read your data into the array as you are
doing. Placing it back to the worksheet can be done in one step though.

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[dolivastro]

Well, your own example is not all that time-consuming -- after all, you
typed it out for this post, and it is only 5 lines.

But your second example is a little confused. If "OriginalMatrix" is
DIM'd as a matrix of variants, then it can't work. If it is DIM'd as a
Range, then you should have written:

Set OriginalMatrix = Sheet1.Range("A1:J10")

Notice there is no ".Value" property at the end. But of course, in
this second example, you should just stick with the original Range
anyway.

In short, I don't think it can get much faster.

Hope this helps (but I don't think it does),
Dom

 

[Guest]

You can use a variant to pick up the data in one go:

Sub aBC()
Dim v As Variant
Dim i As Long, j As Long
' Pick it up
v = Range("A1:Z15").Value
MsgBox "v is an array of 2 dimensions " & _
vbNewLine & _
"(1 to " & UBound(v, 1) & ", 1 to " & _
UBound(v, 2) & ")"

' Process it
For i = 1 To UBound(v, 1)
For j = 1 To UBound(v, 2)
v(i, j) = Int(Rnd() * i * j + 1)
Next
Next

'Put it down
Worksheets.Add After:=Worksheets( _
Worksheets.Count)
ActiveSheet.Range("B9").Resize(UBound(v, 1), _
UBound(v, 2)).Value = v


End Sub

 

[Ken Puls]

Cheers, Tom. Thanks for the correction!

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Alan Beban]

Hard to believe that Ken Puls is a Microsoft MVP.

Dim OriginalArray() As Variant
OriginalArray = Sheets("Sheet1").Range("A1:J10")

Alan Beban

 

[Guest]

Just some added info (for those still using xl5 to xl97)

A variant is always successful.

support for a variant array as Alan shows was added in VBA 6/xl2000 and
later.