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
– Samuel Hulla

2019-04-07 22:18:09 +00:00
Commented Apr 7, 2019 at 22:18 — Samuel Hulla
– Samuel Hulla, Commented Apr 7, 2019 at 22:18

T.M. · Accepted Answer · 2019-04-09 08:15:03Z

1

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

edited Apr 9, 2019 at 8:15

T.M.

10.1k3 gold badges40 silver badges61 bronze badges

answered Apr 7, 2019 at 22:27

Samuel Hulla

7,2808 gold badges46 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

T.M. Over a year ago

corrected assignment in EDIT from arr(3) = … to arr(3, i) = cell.Row :-)

Samuel Hulla Over a year ago

@T.M. Good catch! Thank you :)

Mickeydee · Accepted Answer · 2019-04-07 22:31:12Z

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 7, 2019 at 22:31

Mickeydee

154 bronze badges

1 Comment

Samuel Hulla Over a year ago

This would include the rows he does not want include and also it would be difficult to traverse.

Collectives™ on Stack Overflow

Inserting data in multidimensional array

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related