Old style programming using Arrays

  • Thread starter Thread starter Under Pressure
  • Start date Start date
U

Under Pressure

I have some data in a spreadsheet and want to do some complex things with it.
In the 'old days of GWBASIC' - before spreadsheets - I would set up the
following lines of programming:-

Dim table(40,60)
For i=1 to 40:for j=1 to 60
Read table(i,j)
next j:next i

Then I would analyse the data in the array.

I know that macros can handle the for/next loops but what are the equivalent
lines for setting up a 2 dimensional array and then getting the data from the
spreadsheet?

Any help would be appreciated.

Thanks

Under Pressure
 
Hi,

Try this.

'-----------------------------------
Dim MyTable As Variant
Dim A as long, B As long

With Worksheets("Sheet1")
MyTable = .Range("C5:H40").Value
End With

For a = 1 to ubound(MyTable,1)
For b = 1 to Ubound(MyTable,2)
'Your treatment for each data
X = MyTable(a,b)
Next
Next
'-----------------------------------


"Under Pressure" <[email protected]> a écrit dans le message de
groupe de discussion : (e-mail address removed)...
I have some data in a spreadsheet and want to do some complex things with it.
In the 'old days of GWBASIC' - before spreadsheets - I would set up the
following lines of programming:-

Dim table(40,60)
For i=1 to 40:for j=1 to 60
Read table(i,j)
next j:next i

Then I would analyse the data in the array.

I know that macros can handle the for/next loops but what are the equivalent
lines for setting up a 2 dimensional array and then getting the data from the
spreadsheet?

Any help would be appreciated.

Thanks

Under Pressure
 
Another way is to just work on each cell in the range.

Dim myRng as range
dim myCell as range
dim wks as worksheet

set wks = activesheet

with activesheet
set myrng = .range("x99").resize(40,60) '40 rows by 60 columns
end with

for each mycell in myrng.cells
'do something with that cell
msgbox mycell.value & vblf & mycell.address(0,0)
next mycell
 
Back
Top