Multi Dimensional Array

[andym]

Dear All,

I wish to create a 3 x n multi dimensional array.

I basically want to do the following:

1. Start off in cell "A1"
2. travel down through 100 cells
3. If activecell contains criteria (eg. has a certain value) then
collect this value, and the value of the next 2 corresponding columns
(adjacent cells).
4. Paste these 3 x n cells of data in another area of the sheet.

eg.. out of the 100 cells looped, I may end up with 33 rows of data
that meet the criteria, therefore have a total of 99 elements in the
array.

I have a good understanding of single arrays, but could somebody please
guide me on such a multi-dimensional array? All the searches I have
undertaken so far don't seem to illustrate this type of array
population.

Regards,

andym

 

[Ken Johnson]

Hi Andy,

What you have described sounds to me like a two dimensional array, rows
make up one dimension, with n elements , and the 3 columns are the
second dimension.

Ken Johnson

 

[andym]

Thanks Ken ... you are probably right.

I was presuming my array was going to look like: myArr(ColA,ColB,ColC)


Regards,

andym

 

[NickHK]

All assumes you have Option Base 1
You can create dynamic multi dimensional arrays, but you can only re-dim the
last dimension
In your case, you ideally want an x * 3 array.
So you have 2 choices:
1. Transpose the array so you can dynamically add extra "records" up to the
max of 33
Dim MyArray()
ReDim MyArray(1 To 3, 1 To 2)
'Some code
ReDim Preserve MyArray(1 To 3, 1 To 4)

2. Start off with the max size and potentially have some unfilled elelments
Dim MyArray(1 To 33, 1 To 3)

NickHK

 

[Ken Johnson]

Hi Andy,

Try this

Option Base 1
Public Sub Populate_2D_Array()
Dim My2DArray() As Variant
Dim I As Integer
Dim J As Integer
Dim K As Integer
For I = 1 To 100
If ActiveSheet.Cells(I, 1).Value = 10 Then
K = K + 1
ReDim Preserve My2DArray(K, 3)
For J = 1 To 3
My2DArray(K, J) = ActiveSheet.Cells(I, J).Value
Next J
End If
Next I

MsgBox UBound(My2DArray, 1)
MsgBox My2DArray(UBound(My2DArray), 3)
End Sub

The last two line are just to let you know the size of it first
dimension, then the value of the last element for both dimensions ie
last row and last column.

Ken Johnson

 

[Ken Johnson]

Hi Andy,

forgot to mention the criterion for inclusion in the array was column A
value = 10

Ken Johnson

 

[andym]

Thanks Ken and Nick..

while you posting your reply I took your initial thoughts and created
the following:

Sub arraytest2()

Dim Arr(0 To 299) As String
Dim N As Integer, M As Integer
Dim i As Integer, x As Integer

i = 0

Range("A33").Activate

For x = 1 To 100
If Left(ActiveCell.Value, 1) = 2 Then

For M = 0 To 1
Arr(i) = ActiveCell.Value
Arr(i + 1) = ActiveCell.Offset(0, 2).Value
Arr(i + 2) = ActiveCell.Offset(0, 3).Value
i = i + 3
ActiveCell.Offset(1, 0).Activate
Next M

End If
ActiveCell.Offset(1, 0).Activate
Next x

For N = 0 To 299
Debug.Print Arr(M)
Next N

End Sub

This works fine, but is obviously restrictive. I will now use your
example and modify it. I appreciate everybody's help in this.

Regards,

andym

 

[Ken Johnson]

Hi Andy,

Please forgive my stupidity!

As Nick stated, when using ReDim Preserve, you can only change the size
of the LAST dimension, so you forget about rows staying as rows and
columns staying as columns.

When the code is looking at your worksheet it is always taking values
from 3 columns but the number of rows it takes data from is increasing
up to a final unknown number (Although it can't be bigger thwn the
total number of rows being searched.

Because of these facts(rows are changing, columns fixed at 3,can only
resize array's 2nd dimension) you usually get your code to feed the
sheet column values into the array's fixed first dimension and the
sheet's row values into the array's resizeable last dimension.
So values on the worksheet that appear on the worksheet as 30 rows and
3 columns would produce an array with only 3 rows and 30 columns. If
you then need to place such an array back onto the worksheet you can
use the Transpose worksheet function.

The following code is (I hope) correct...

Option Base 1
Public Sub Populate_2D_Array()
Dim My2DArray() As Variant
Dim I As Integer
Dim J As Integer
Dim K As Integer
For I = 1 To 100
If ActiveSheet.Cells(I, 1).Value = 10 Then
K = K + 1
ReDim Preserve My2DArray(3, K)
For J = 1 To 3
My2DArray(J, K) = ActiveSheet.Cells(I, J).Value
Next J
End If
Next I
For I = 1 To UBound(My2DArray, 2)
Debug.Print My2DArray(1, I) & ", " & _
My2DArray(2, I) & ", " & _
My2DArray(3, I)
Next
End Sub

Check out the values printed in the Immediate window.

Ken Johnson

 

[Ken Johnson]

Hi Andy,

This version places the array values onto the worksheet starting at G1.
Just change the G1 to suit your needs...

Public Sub Populate_2D_Array()
Dim My2DArray() As Variant
Dim I As Integer
Dim J As Integer
Dim K As Integer
For I = 1 To 100
If ActiveSheet.Cells(I, 1).Value = 10 Then
K = K + 1
ReDim Preserve My2DArray(3, K)
For J = 1 To 3
My2DArray(J, K) = ActiveSheet.Cells(I, J).Value
Next J
End If
Next I
ActiveSheet.Range("G1").Resize(UBound(My2DArray, 2), _
UBound(My2DArray, 1)) = WorksheetFunction.Transpose(My2DArray)
End Sub

Ken Johnson

 

[Ken Johnson]

Hi Andy,

I've adopted your worksheet range and criterion. Again, change G1 to
suit your needs...

Public Sub Populate_2D_Array()
Dim My2DArray() As String
Dim I As Integer
Dim J As Integer
Dim K As Integer
For I = 33 To 132
If Left(ActiveSheet.Cells(I, 1).Value, 1) = 2 Then
K = K + 1
ReDim Preserve My2DArray(3, K)
For J = 1 To 3
My2DArray(J, K) = ActiveSheet.Cells(I, J).Value
Next J
End If
Next I
ActiveSheet.Range("G1").Resize(UBound(My2DArray, 2), _
UBound(My2DArray, 1)) = WorksheetFunction.Transpose(My2DArray)
End Sub

Ken Johnson

 

[andym]

Ken,

many thanks for your explainations and your examples. I can now get the
data I require, and have a good base if I need to modify my range in
the future, so I really appreciate the time you have put in.

Like most occassions one solution poses another challenge!!! The range
in which I offload the array is a feed for some array formulas to do
some calculating.

Obviously as each cell in the range is populated out of the array, the
array formulas recalc causing great time delays. In my code I have
added the "With Application.Calculation = xlCalculationManual" at the
beginning, and have set it back to auto at the end.

However, I can't get my array formulas to recalc unless I type the
whole thing in again.

Do you, or anybody else out there, have any solutions to this? There
must be something simple I am missing!!

I will add this question to a new post.

Thanks again for your help.


Regards,
andym

 

[Ken Johnson]

Hi Andy,

I spotted your other post and thought it a pretty interesting problem.
I've tried a few things but keep on getting nowhere.

How did Tim's suggestion go?

My idea, which might not be doable, was for each cell in the
Range("A1:C100") on sheet2 look for its dependents (cells that contain
a formula that refer to it). I spotted the Dependents property in the
Object Browser and Help states "Returns a Range object that represents
the range containing all the dependents of a cell". However, I've never
used it before and have been struggling to make use of it. My main
problem is dealing with the error that occurs when there aren't any
dependents.

If I manage to build up a Range of all the dependents for the
Range("A1:C100") I will then try the following for each dependent..

copy the formula to a string variable, then put the formula back into
the dependent cell using...

FormulaArray = the string variable value

and having autocalculation on (but then that will probably result in
the time delay again!?)

I got this expression using the macro recorder for just pressing Ctrl +
Shift + Enter when a cell with an array formula is entered
(Selection.FormulaArray = string for formula)


I could be barking up the wrong tree entirely or I'm doing something
silly when I try to deal with the error resulting from asking for the
dependents of a cell that has none.


Ken Johnson