VBA Deriving array from array based on its value

[CapTheCap]

I got a requirement to do on VBA Excel. No coding expertise here. It's about converting array data into another array. (The bullet points are just to represent a process to do)

For each cell, columnAnsDesc, data is like a 3-dimension array each with a field name (Label/Num/Use), like this <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">{ {"Label":"XX","Num":3,"Use":1}, {"Label":"YY","Num":4,"Use":1}, {"Label":"EE","Num":5,"Use":1}, {"Label":"GG","Num":7,"Use":0} }</code>

• Then we need an array of value in dimension "Label" whose dimension <code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">"Use" = 1</code> so would be like this <code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">{"XX","YY","EE"}</code> Let's call it arrayLabelUse

Next, in another cell in the same row, says columnAnsCode, is <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">{"0":0,"1":1,"2":1}</code> which is a one dimension array, and always have the same number of elements as that of arrayLabelUse

• So now we have <code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">{"XX","YY","EE"}</code> and <code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">{"0":0,"1":1,"2":1}</code>
• And we need the final return being the Label with the 1 value after <code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">":"</code> in the columnAnsCode

Therefore, final output is like this <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">{"YY","EE"}</code>

The field name of each element makes it different from a normal array, is there a name for this type of array?
Thank you very much

 

[CapTheCap]

Here is my VB-beginner code, it has a bunch of error (red text) starting from IF ISERROR.

Appreciate any correction at all. Thank you very much

Note: aaa is like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; color: rgb(0, 0, 0); line-height: 17.804800033569336px;">{{"Label":"XX","Num":3,"Use":1},{"Label":"YY","Num":4,"Use":1},{"Label":"EE","Num":5,"Use":1}, {"Label":"GG","Num":7,"Use":0} }</code> ashort is like<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; color: rgb(0, 0, 0); line-height: 17.804800033569336px;">{"XX","YY","EE"}</code> bbb is like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; color: rgb(0, 0, 0); line-height: 17.804800033569336px;">{"0":0,"1":1,"2":1}</code> afinal is like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 14px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; color: rgb(0, 0, 0); line-height: 17.804800033569336px;">{"YY","EE"}
</code>

Sub Anstran()
    Dim aaa As Variant
    Dim bbb As Variant
    Dim ause As Long
    Dim bshort As Long


Do
        'for first dimension
        For index0 = 0 To aaa.GetUpperBound(0)
        i = 0
            'for second dimension
            For index1 = 0 To aaa.GetUpperBound(1)
                'for first dimension
                For index2 = 0 To aaa.GetUpperBound(2)
                    IF ISERROR(FIND(index2, ""Use":1", 1)) = "False"
                    'cleaning this "Label":"XX" to this XX
                    THEN ause(i) = Replace(Replace(index0, ""Label":", ""), """, "")
                    End If
            
                Next
        i = i + 1
        'Go to next element
        Next
        
        'After finish creating ause array from aaa array, evaluate bbb (the cell to the right of aaa)
        bbb = ActiveCell.Offset(0, 1).Value
        
        'make JSON data to normal array like {0,0,1}
        For index0 = 0 To bbb.GetUpperBound(0)
        g = 0
            bshort(g) = Right(bbb(index0), 1).ToString
        g = g + 1
        Next
        
        'for each element of bbb
        For index0 = 0 To bbb.GetUpperBound(0)
            j = 0
            k = 0
            'if value is 1 then create afinal() with the element in ause()
            IF bshort(j) = 1
            THEN afinal(k) = ause(j) AND k=k+1
            End If
        j = j + 1
        Next
        'display afinal array in another column
        ActiveCell.Offset(0, 1).Value = afinal()
        
        'Done with this row, go on to the next row
        ActiveCell.Offset(1, 0).Select
        aaa = ActiveCell.Value
        
        Next


Loop Until ActiveCell.Value = ""


End Sub