how to display result of Scripting dictionary

ravaz

Board Regular
Joined
Mar 25, 2008
Messages
192
How to I get the results of the below code into cell "A2" of sheet "Inv"

Code:
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

Board Regular
Joined
Mar 25, 2008
Messages
192
Sheet db and Sheet INV both have the same format but sheet Inv shows only data for vch type INVOICE
DateVch TypeVch No.ParticularsNarrationsCurr ROE F Debit F Credit Debit Credit
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 - 4,705.89 - 7,446,976.81
1-JanJournal Kyondo General TradersBeing bought of Apple Magic two wireless MousesUSD 1,582.48 91,411.77 - 144,657,297.79 -
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 - 91,411.77 - 144,657,297.79
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 8,597.00 - 13,604,580.56 -
1-JanJournal Kyondo General TradersBeing bought of Apple Magic two wireless MousesUSD 1,582.48 - 8,597.00 - 13,604,580.56
1-JanJournal Smooth General Supply LtdBeing bought of Apple Magic two wireless MousesUSD 1,582.48 33,532.83 - 53,065,032.82 -
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 - 33,532.83 - 53,065,032.82
1-JanJournal New Image Transportation Co.Being bought of Apple Magic two wireless MousesUSD 1,582.48 76,652.94 - 121,301,744.49 -
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 - 76,652.94 - 121,301,744.49
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 5,500.00 - 8,703,640.00 -
1-JanJournal New Image Transportation Co.Being bought of Apple Magic two wireless MousesUSD 1,582.48 - 5,500.00 - 8,703,640.00
1-JanJournal Directors Loan A/CBeing bought of Apple Magic two wireless MousesUSD 1,582.48 7,000.00 - 11,077,360.00 -
1-JanJournal New Image Transportation Co.Being bought of Apple Magic two wireless MousesUSD 1,582.48 - 7,000.00 - 11,077,360.00
1-JanJournal Computers, Cameras,Printers & FaxBeing bought of Apple Magic two wireless MousesUSD 1,600.00 240.00 - 384,000.00 -
1-JanJournal Elite Computers (T) LtdBeing bought of Apple Magic two wireless MousesUSD 1,600.00 - 240.00 - 384,000.00
1-JanPurchase1Funky MonkeyCost for Tigo SMS TVC Shoot for 1yearUSD 1,590.00 - 13,050.00 - 20,749,500.00
1-JanPurchase1Cos of TVC ProductionCost for Tigo SMS TVC Shoot for 1yearUSD 1,590.00 13,050.00 - 20,749,500.00 -
1-JanPurchase2The Guardian LimitedBeing Investment Opportunity -SHILOHTZS 1.00 - - - 803,344.00

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

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708

ADVERTISEMENT

Like this:

Code:
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

Board Regular
Joined
Dec 19, 2011
Messages
75
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,708

ADVERTISEMENT

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

GopalaKrishnaJ

Board Regular
Joined
Dec 19, 2011
Messages
75
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top