+ Reply to Thread
Results 1 to 12 of 12

Multi Dimensional Array

  1. #1
    andym
    Guest

    Multi Dimensional Array

    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


  2. #2
    Ken Johnson
    Guest

    Re: Multi Dimensional Array

    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


  3. #3
    NickHK
    Guest

    Re: Multi Dimensional Array

    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


    "andym" <apmeehan@gmail.com> wrote in message
    news:1152071340.656505.309670@m79g2000cwm.googlegroups.com...
    > 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
    >




  4. #4
    andym
    Guest

    Re: Multi Dimensional Array

    Thanks Ken ... you are probably right.

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


    Regards,

    andym


    Ken Johnson wrote:

    > 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



  5. #5
    Ken Johnson
    Guest

    Re: Multi Dimensional Array

    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


  6. #6
    Ken Johnson
    Guest

    Re: Multi Dimensional Array


    Hi Andy,

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

    Ken Johnson


  7. #7
    andym
    Guest

    Re: Multi Dimensional Array

    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 wrote:

    > Hi Andy,
    >
    > forgot to mention the criterion for inclusion in the array was column A
    > value = 10
    >
    > Ken Johnson



  8. #8
    Ken Johnson
    Guest

    Re: Multi Dimensional Array

    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


  9. #9
    Ken Johnson
    Guest

    Re: Multi Dimensional Array

    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


  10. #10
    Ken Johnson
    Guest

    Re: Multi Dimensional Array

    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


  11. #11
    andym
    Guest

    Re: 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



  12. #12
    Ken Johnson
    Guest

    Re: Multi Dimensional Array

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1