Dynamic multi dimensional array

[mo2783]

I am trying to store information from a spreadsheet to use later but in
another worksheet. The information i need to store relates to records as such

so for instances I have a the following information on a worksheet

A B C
1 400 400 400
2 428 378 421
3 1/6 10/6 22/6
4 70 80 88
5 100% 78% 91%

what I need to do is take this information for each Column and then transfer
it to another worksheet after doing some calculating and meeting other
criteria.

I was thinking of storing the information from the worksheet above in to an
Array....so something like this


1 2 3 4 5
1
2
3
.
.
x

I want to take this array and do whatever i need to do to the information.

I need help on how to create and store information to an array if that is a
good solutions. Otherwise please suggest a better way to store and retrieve
this data.

 

[SkipVought]

Hi,

Isn't a spreadsheet as good enough and array for storing and doing something later, unless you intend to exceed the row & column limits of Excel 2003 or earlier, or am I missing something?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...sorry,

as good an array as....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

You can transfer a range to and from an array similar to the following:

Public Sub SimpleTransform()
    Dim rx As Integer
    Dim cx As Integer
    Dim vRange As Variant
    
    ' Input the original values ...
    vRange = Sheet1.Range("A1:C5")
    
    For rx = LBound(vRange, 1) To UBound(vRange, 1)
        For cx = LBound(vRange, 2) To UBound(vRange, 2)
            'Do your calculations on vRange(rx, cx) here
        Next
    Next
    
    ' Output the transformed values to another sheet ...
    Sheet2.Range("B2:D6") = vRange
    
End Sub