Inserting data in multidimensional array

Question

I have to move data in my input files to another workbook. The data is structured in worksheets as hardcoded input as below where the column with all the identifiers is a named range called "INPUT_MARKER".

    IQ_SALES    100     200     300
    IS_MARGIN    20      30      40
    IQ_EBITDA    50      30      20

I only have to move some of the data. So for instance in the above I would only have to move the IQ_SALES data and IQ_EBITDA data. So I need to understand how to create an array of arrays with only the data that is needed.

The code below compares the data in the INPUT_MARKER column with elements in the array called "identifierArray" and I then intend to insert all of the row data in the multidimensional array called "bigDataArray". I have tried several approaches but have not been able to make this work. Would much appreciate any help. I have left out some of the redundant code in the below such that only the code pertaining to this problem is included.

    Sub Update()

    Dim identifierArray(), bigDataArray() As Variant

    Application.ScreenUpdating = False
    Application.CutCopyMode = False

    'Definition of the array of data that is to be transferred to the targetModel
    identifierArray = Array("IQ_SALES", "IQ_EBITDA")
    ReDim bigDataArray(1 To UBound(identifiersArray))

    With Workbooks(sourceModel).Sheets("DATA")
        For Each c In .Range("INPUT_MARKER")
            For Each element In identifierArray
                If element = c.Value Then
                    'To construct bigDataArray by inserting row data every time element equals c.Value

                End If
            Next element
        Next c
    End With

    End Sub

Does your `INPUT_MARKER` range begins with Column `A`? – Samuel Hulla Apr 07 '19 at 22:18 — Samuel Hulla, Apr 07 '19 at 22:18

score 1 · Accepted Answer · edited Apr 09 '19 at 08:15

I've tackled a similar issue recently. This can be handled with a multi-dimensional array from the look of things

Though as a predisposition I'd recommend checking reference on dynamic multi-dimensional arrays

Private Sub fill_array()

    Dim arr() As String
    Dim i As Integer: i = 0
    Dim cell As Range
    Dim ws As Worksheet: Set ws = Sheets("DATA")

    For Each cell In ws.Range("INPUT_MARKER")
        If ws.Cells(cell.Row, 1) = "IQ_SALES" Or ws.Cells(cell.Row, 1) = "IQ_EBITDA" Then
            ReDim Preserve arr(0 To 2, i)
            arr(0, i) = ws.Cells(cell.Row, 2)
            arr(1, i) = ws.Cells(cell.Row, 3)
            arr(2, i) = ws.Cells(cell.Row, 4)
            i = i + 1
        End If
    Next cell
End Sub

So your array will have the structure ofarr(x, y), where:

x - [0;2] - will be the 3 columns of data you want to store
y - n - index of the array (with only IQ_SALES and IQ_EBITDA being added)

EDIT:

This is of course presuming, your data "INPUT_MARKER" starts at Column A

Also as an extra tip, if you want to also store information of the arrays source - in resemblence of a primary key, you can increment the first dimension

ReDim Preserve arr(0 to 3, i)
arr(3, i) = cell.Row              ' edited (instead of arr(3)= …)

and use example the cell.Row as a reference as to where the data was obtained from, in order to reverse trace the data

corrected assignment in EDIT from `arr(3) = …` to `arr(3, i) = cell.Row` :-) — T.M., Apr 09 '19 at 08:17

score 0 · Answer 2 · answered Apr 07 '19 at 22:31

0

If you know the range of the values you want to pick you can shortcut using:

Dim bigDataArray() As Variant
bigDataArray = Range(A1:D4)

This will set up the array with the same size as the range you pick up, Then you can output the specific values you want from the array.

answered Apr 07 '19 at 22:31

Mickeydee

15
4

This would include the rows he does not want include and also it would be difficult to traverse. – Samuel Hulla Apr 07 '19 at 23:02

Inserting data in multidimensional array

2 Answers2