CODEWITHSUNDEEP
excelvba
ChartProblems
ChartProblems

Reputation: 437

Inserting data in multidimensional array

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

Upvotes: 0

Views: 272

Answers (2)

Samuel Hulla
Samuel Hulla

Reputation: 7089

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

Upvotes: 1

Mickeydee
Mickeydee

Reputation: 15

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.

Upvotes: 0

Related Questions