My original post on this appears to have disappeared, so here goes again.

I want to do some complex analysis on some data in a spreadsheet. I would
like to use a macro but don't know how to 'read' the data from the
spreadsheet into a 2 dimensional array. Back in the old days of programming
in GWBASIC, I would have used lines as follows :

DIM TABLE(100,40)
FOR I = 1 TO 100 : FOR J= 1 TO 4
READ TABLE(I,J)
NEXT J: NEXT I

I know that macros can handle FOR/NEXT loops but what about getting the data
from a range say A1:D100

Thanks again in advance

Under Pressure

DIM TABLE(100,40)
FOR I = 1 TO 100
FOR J= 1 TO 4

READ TABLE(I,J) = cells(I,J)

NEXT J
NEXT I


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168841

Microsoft Office Help

On Mon, 11 Jan 2010 09:43:01 -0800, Under Pressure
wrote:

My original post on this appears to have disappeared, so here goes again.

I want to do some complex analysis on some data in a spreadsheet. I would
like to use a macro but don't know how to 'read' the data from the
spreadsheet into a 2 dimensional array. Back in the old days of programming
in GWBASIC, I would have used lines as follows :

DIM TABLE(100,40)
FOR I = 1 TO 100 : FOR J= 1 TO 4
READ TABLE(I,J)
NEXT J: NEXT I

I know that macros can handle FOR/NEXT loops but what about getting the data
from a range say A1:D100

Thanks again in advance

Under Pressure



Here is an example that you can modify according to your needs:

Function complex_analysis(r As Range)
Dim a() As Double
ReDim a(1 To r.Rows.Count, 1 To r.Columns.Count)
s = 0
' transfer the data from Range r to Array a
For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
a(i, j) = r(i, j)
Next j
Next i

' do complex analyis, summing is just an example
For i = 1 To UBound(a, 1)
For j = 1 To UBound(a, 2)
s = s + a(i, j)
Next j
Next i

complex_analysis = s

End Function

Sub test()
MsgBox complex_analysis(Range("A1:D100"))
End Sub

If you are reading values in from a worksheet, you don't need a loop at all.
This code will load up the Table array as you have indicated...

Dim TABLE() As Variant
TABLE = Range("A1:D20").Value

Just change my example range of A1:D100 to whatever range of cells you want
to put into your array variable.

--
Rick (MVP - Excel)


"Under Pressure" wrote in message
...
My original post on this appears to have disappeared, so here goes again.

I want to do some complex analysis on some data in a spreadsheet. I would
like to use a macro but don't know how to 'read' the data from the
spreadsheet into a 2 dimensional array. Back in the old days of
programming
in GWBASIC, I would have used lines as follows :

DIM TABLE(100,40)
FOR I = 1 TO 100 : FOR J= 1 TO 4
READ TABLE(I,J)
NEXT J: NEXT I

I know that macros can handle FOR/NEXT loops but what about getting the
data
from a range say A1:D100

Thanks again in advance

Under Pressure