Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Got a dynamic array (arr1) that changes in size (currently 2182 rows x 17 columns)

    Looping through the arr1 I'm searching for a user provided variable in a specific column (say column 2). The value is a given criteria (eg. "company name") If the loop encounters a company that meets the criteria, it adds the value in each column to another array (arr2).

    In this way, once I'm through arr1, I have in arr2 a subset of arr1 representing only the values I need to work with.

    My problem is to fill arr2. Redim preserve will not do it for me as it only allows adjustment of the last dimension of the array.

    Other people must have had similar problems . How would you normally go about this??

    Below is my current code:

    Dim varGetArrayTransactions as Variant

    Public Sub Test_TransStep()
    Dim arrTransList()
    Dim sRangeAdr As String
    Dim sCoName As String
    Dim c, d, e

    e = 1

    'For test purposes - set Company name
    sCoName = "MyCompany"

    'Returns range as string and has entire array loaded in varGetArrayTransactions
    sRangeAdr = Get_RangeAddresses(sSheetName:=SHT_TRANSACTIONS, sRowToGet:="Range", arrRangeLoad:=varGetArrayTransactions)

    'Loop entire array
    For c = 1 To UBound(varGetArrayTransactions, 1)

    'Can we find a matching company?
    If sCoName = varGetArrayTransactions(c, 2) Then

    'Match found - add values from each column to new array (arrTransList)
    For d = 1 To UBound(varGetArrayTransactions, 2)
    'This bit doesn't work...!!#!"#!#!"
    ReDim Preserve arrTransList(e, UBound(varGetArrayTransactions, 2))
    arrTransList(e, d) = varGetArrayTransactions(c, d)
    Debug.Print "Item (" & e & "," & d & "): " & arrTransList(e, d)
    Next d

    e = e + 1

    End If

    Next c



    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Make arr2 a 1D array of arrays instead of a 2D array, or switch the rows and columns over and then transpose it at the end.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Not sure I understand what you mean by "Make arr2 a 1D array of arrays" - got an example?

    I tried the transpose bit for starters, but I find it to be a bit messy to work with, when I have many "rows".

    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In what way is transpose messy?

    Can't be specific since you only posted a fraction of your code, but something along the lines of:
    Code:
    ReDim Preserve arrTransList(e)
    arrTransList(e) = varGetArrayTransactions(c)
    You would of course have to rewrite your called function to return the whole array, not one value at a time.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Got you on that - thanks for the tip.

    I have decided to take a slightly different approach with a fast double iteration, in which I first find the exact number of matches (the UBOUND - then I Redim in accordance with that before entering the loops.

    Code looks like this now

    e = 1
    eMax = 0

    For c = 1 To UBound(varGetArrayTransactions, 1)
    If sCoName = varGetArrayTransactions(c, 2) Then eMax = eMax + 1
    Next c
    ReDim arrTransList(eMax, UBound(varGetArrayTransactions, 2))

    For c = 1 To UBound(varGetArrayTransactions, 1)
    If sCoName = varGetArrayTransactions(c, 2) Then
    For d = 1 To UBound(varGetArrayTransactions, 2)
    arrTransList(e, d) = varGetArrayTransactions(c, d)
    Debug.Print "Item (" & e & "," & d & "): " & arrTransList(e, d)
    Next d
    e = e + 1
    End If
    Next c


    Appreciate your efforts on this.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

Posting Permissions

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