how to display result of Scripting dictionary

[ravaz]

How to I get the results of the below code into cell "A2" of sheet "Inv"

Private Sub Worksheet_Activate()
Dim x, i, ii, u(), dic As Object, z As String
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
x = Sheets("DB").Range("a1").CurrentRegion.Resize(, 11).Value
rcnt = UBound(x, 1)
ccnt = UBound(x, 2)
For i = 1 To rcnt
If x(i, 2) = "INVOICE" And x(i, 10) > 0 Then
z = x(i, 3)
    If Not dic.exists(z) Then
        ReDim u(1 To ccnt)
            For ii = 1 To ccnt: u(ii) = x(i, ii): Next
            dic.Add z, u
    End If
End If
Next i
Rows("2:" & Rows.Count).ClearContents
End Sub

 

[yky]

:

If you think that's the answer, please mark it as the answer.

 

[Fluff]

@yky Preeti V cannot mark as solution, only the OP can do that

 

[ravaz]

I know, but in this case the object returned is an array of arrays. Not just an array. You can't write this directly to a sheet without looping

Sub test()
Dim x, i, ii, u(), dic As Object, z As String
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
x = Sheets("DB").Range("a1").CurrentRegion.Resize(, 11).Value
rcnt = UBound(x, 1)
ccnt = UBound(x, 2)
For i = 1 To rcnt
If x(i, 2) = "INVOICE" And x(i, 10) > 0 Then
z = x(i, 3)
    If Not dic.exists(z) Then
        ReDim u(1 To ccnt)
            For ii = 1 To ccnt: u(ii) = x(i, ii): Next
            dic.Add z, u
    End If
End If
Next i
'Rows("2:" & Rows.Count).ClearContents

Sheets("inv").Cells(2, 1).Resize(dic.Count, ccnt).Value = Application.Transpose(Application.Transpose(dic.items))
End Sub