Multi Dimensional Array
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 wrote:
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
|