Create array from array – redim preserve

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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”.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote