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

 

[hiker95]

ravaz,

What version of Excel are you using?

Can you post the raw data, and, post the results you are looking for?


Can we have a screenshot of each worksheet DB, and Inv?


To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.

 

[ravaz]

Sheet db and Sheet INV both have the same format but sheet Inv shows only data for vch type INVOICE

Date	Vch Type	Vch No.	Particulars	Narrations	Curr	ROE	F Debit	F Credit	Debit	Credit
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	4,705.89	-	7,446,976.81
1-Jan	Journal		Kyondo General Traders	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	91,411.77	-	144,657,297.79	-
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	91,411.77	-	144,657,297.79
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	8,597.00	-	13,604,580.56	-
1-Jan	Journal		Kyondo General Traders	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	8,597.00	-	13,604,580.56
1-Jan	Journal		Smooth General Supply Ltd	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	33,532.83	-	53,065,032.82	-
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	33,532.83	-	53,065,032.82
1-Jan	Journal		New Image Transportation Co.	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	76,652.94	-	121,301,744.49	-
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	76,652.94	-	121,301,744.49
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	5,500.00	-	8,703,640.00	-
1-Jan	Journal		New Image Transportation Co.	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	5,500.00	-	8,703,640.00
1-Jan	Journal		Directors Loan A/C	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	7,000.00	-	11,077,360.00	-
1-Jan	Journal		New Image Transportation Co.	Being bought of Apple Magic two wireless Mouses	USD	1,582.48	-	7,000.00	-	11,077,360.00
1-Jan	Journal		Computers, Cameras,Printers & Fax	Being bought of Apple Magic two wireless Mouses	USD	1,600.00	240.00	-	384,000.00	-
1-Jan	Journal		Elite Computers (T) Ltd	Being bought of Apple Magic two wireless Mouses	USD	1,600.00	-	240.00	-	384,000.00
1-Jan	Purchase	1	Funky Monkey	Cost for Tigo SMS TVC Shoot for 1year	USD	1,590.00	-	13,050.00	-	20,749,500.00
1-Jan	Purchase	1	Cos of TVC Production	Cost for Tigo SMS TVC Shoot for 1year	USD	1,590.00	13,050.00	-	20,749,500.00	-
1-Jan	Purchase	2	The Guardian Limited	Being Investment Opportunity -SHILOH	TZS	1.00	-	-	-	803,344.00

<colgroup><col><col><col><col><col span="2"><col span="3"><col span="2"></colgroup><tbody>
</tbody>

 

[ravaz]

Link to Box Net

https://www.box.com/s/e970c23fd39d73ebd95d

 

[Kyle123]

Like this:

Dim keyskeys = dic.keys
For x = 0 To dic.Count - 1
    Sheets("inv").Cells(x + 2, 1).Resize(1, ccnt).Value = dic.Item(keys(x))
Next x

 

[GopalaKrishnaJ]

Or else Directly you can go with one line without looping in keys

Sheets("inv").Cells(1, 1).Resize(1, dic.count).Value = dic.items

 

[Kyle123]

That won't work since dic.items is an array of arrays, not a 2 dimensional array

 

[GopalaKrishnaJ]

To get all the items which are assigned to dictionary, there is no need to make a loop.
We can get it with dic.items

 

[Kyle123]

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

 

[Preeti V]

:

Or else Directly you can go with one line without looping in keys

Sheets("inv").Cells(1, 1).Resize(1, dic.count).Value = dic.items