Faster output to Sheet from a collection of Dictionaries My code is currently offloading parsed data into multiple spreadsheets, but it's pretty slow: about 110k rows and 35 columns in 3.5 minutes.
I was wondering if there were a more efficient way of looping through my collection of dictionaries.

Sub getData(Parsed As Object, company As String, a, wb)
        'Parsed contains a collection of dictionaries parsed from a JSON input'

        Dim oSheet As Variant
        Dim i As Long
        Dim j As Long
        For Each oSheet In ActiveWorkbook.Sheets
            'Identify the sheet containing the name of the company'
            Dim matchSheet As String
            If InStr(UCase(oSheet.Name), UCase(company)) Then
                matchSheet = oSheet.Name
            Else
                GoTo nextIteration
            End If
                'Create a 2d array of i number of dictionaries and j number of element in each dictionary'
                Dim Values As Variant
                ReDim Values(Parsed.Count, ArrayLen(a))

                Dim Value As Dictionary
                Dim key As Variant

                i = 1
                j = 0
                wb.Sheets(matchSheet).Select 'macro not working without selecting the sheet'
                wb.Sheets(matchSheet).Cells.Clear 'Clearing the data in the company Sheet'

                'For loops populating the Values array'
                For Each Value In Parsed
                    For Each key In a
                        Values(i, j) = Value(key)
                        j = j + 1
                    Next key
                    j = 0
                  i = i + 1
                Next Value

                'populating the needed sheet range with the parsed data'
                wb.Sheets(matchSheet).Range(Cells(1, 1), Cells(Parsed.Count, ArrayLen(a))) = Values
                'populationg the headers for that data'
                wb.Sheets(matchSheet).Range(Cells(1, 1), Cells(1, ArrayLen(a))).Value = a
                wb.Sheets(matchSheet).Range("D:D").NumberFormat = "General"
nextIteration:
        Next oSheet

End Sub


Here is an exemple of  what my API request returning 2 deals with 3 keys out of 37:

[
   {
      "DealNo": "11111",
      "DealDate": "2010-01-01",
      "Quantity": "1000"
   },
   {
      "DealNo": "11112",
      "DealDate": "2010-01-02",
      "Quantity": "2000"
   }
]


And here is the structure of the data after it has been parsed in excel:
A couple of review points.

Goto. Using Goto is a real bad smell. In your case, you can remove the spaghetti very easily. 


Sub getData(Parsed As Object, company As String, a, wb)
        'Parsed contains a collection of dictionaries parsed from a JSON input'
        Dim oSheet As Variant
        Dim i As Long
        Dim j As Long
        For Each oSheet In ActiveWorkbook.Sheets
            'Identify the sheet containing the name of the company'
            Dim matchSheet As String
            If InStr(UCase(oSheet.Name), UCase(company)) Then
                matchSheet = oSheet.Name
            Else
                GoTo nextIteration
            End If
                ' [Main Code Block]
nextIteration:
        Next oSheet
End Sub



Becomes

Sub getData(Parsed As Object, company As String, a, wb)
        'Parsed contains a collection of dictionaries parsed from a JSON input'
        Dim oSheet As Variant
        Dim i As Long
        Dim j As Long
        For Each oSheet In ActiveWorkbook.Sheets
            'Identify the sheet containing the name of the company'
            Dim matchSheet As String
            If InStr(UCase(oSheet.Name), UCase(company)) Then
                matchSheet = oSheet.Name
                ' [Main Code Block]
            End If
        Next oSheet
End Sub


Much cleaner!

Select. I can see why your code does not work without using Select. And you do not need it. You have unqualified references to Range objects (in this case Cells) that are acting on the active sheet. By selecting the sheet, you are changing the active sheet but there is no guarantee that it is going to stay that way!


            wb.Sheets(matchSheet).Range(Cells(1, 1), Cells(Parsed.Count, ArrayLen(a))) = Values
            'populationg the headers for that data'
            wb.Sheets(matchSheet).Range(Cells(1, 1), Cells(1, ArrayLen(a))).Value = a
            wb.Sheets(matchSheet).Range("D:D").NumberFormat = "General"



Is better expressed in a With statement for brevity and ease of reading (note that the "." are also in front of the Cells, this is the critical change to your original code).

            With wb.Sheets(matchSheet)
                .Range(.Cells(1, 1), .Cells(Parsed.Count, ArrayLen(a))) = Values
                'populating the headers for that data'
                .Range(.Cells(1, 1), .Cells(1, ArrayLen(a))).Value = a
                .Range("D:D").NumberFormat = "General"
            End With


ReDim. Probably not a huge issue, but you ReDim every loop instance on values that are passed in at the start. Set your Values array up once before you enter the loop. By the looks of your code, you clobber the values in the array each time you go through the loop, so you don't even have to clear it.

I would guess that your biggest performance hit is iterating through the dictionaries. If you could bring your data in in a cleaner fashion, it may help your performance.