PDA

View Full Version : [SOLVED] One more scripting.dictionary question



idnoidno
06-19-2017, 12:11 AM
Thanks to the people on the site who have provided learning scripting.dictionary information.
I may not learn enough, so I still can not solve many problems, I sorted out a few questions, please help, let me have the opportunity to learn more.:bow:


19539

xld
06-19-2017, 12:34 AM
And the question is?

idnoidno
06-19-2017, 12:50 AM
And the question is?
The data is left, and the result is right.

mdmackillop
06-19-2017, 01:01 AM
I would not use the dictionary object to "solve" this, although it might be forced as a tool. Why not post the code you are trying to use so we can advise on your approach and any errors.

idnoidno
06-19-2017, 01:39 AM
I would not use the dictionary object to "solve" this, although it might be forced as a tool. Why not post the code you are trying to use so we can advise on your approach and any errors.
If you do not use DICTIONARY method, use ARRAY method can get that kind of results?

mdmackillop
06-19-2017, 01:58 AM
I'd use "Remove Duplicates", "Countifs" and "SumIfs"

idnoidno
06-19-2017, 02:11 AM
I'd use "Remove Duplicates", "Countifs" and "SumIfs"

Wow, it's really a good way,dude.
I will try the functions what you mentioned to solve the problem.

mana
06-19-2017, 03:54 AM
You can also use pivot table.

idnoidno
06-19-2017, 04:40 AM
You can also use pivot table.
If possible I would like to learn dictionary method from you,wouldn't you?

mana
06-19-2017, 04:44 AM
Option Explicit


Sub test()
Dim dic As Object
Dim v
Dim i As Long, s

Set dic = CreateObject("scripting.dictionary")

v = Range("a1").CurrentRegion.Value
For i = 2 To UBound(v)
s = v(i, 1) & vbTab & v(i, 2)
If Not dic.exists(s) Then dic(s) = Array(, , 0, 0)
dic(s) = Array(v(i, 1), v(i, 2), dic(s)(2) + 1, dic(s)(3) + v(i, 3))
Next

With Range("F1")
.CurrentRegion.ClearContents
.Resize(, 4).Value = [{"item1","item2","Number","sum"}]
.Offset(1).Resize(dic.Count, 4).Value = _
Application.Transpose(Application.Transpose(dic.items))
.CurrentRegion.Sort key1:=.Columns(1), Header:=xlYes
End With

End Sub

idnoidno
06-19-2017, 04:52 AM
The code is so simple, but it's hard to understand.
I would like to ask you those sites can learn dictionary method, you will not tell me also
http://www.snb-vba.eu/VBA_Dictionary_en.html?:hi:

idnoidno
06-19-2017, 07:10 AM
Sub redupli()
Dim arr
arr = Range("a1").CurrentRegion.Value
Worksheets.Add.Name = "temp"
[a1].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
Range("a1:c" & Cells(Rows.Count, 1).End(xlUp).Row) _
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

The first step.

idnoidno
06-19-2017, 07:44 AM
For i = 2 To UBound(arr)
Cells(i, 4) = Application.WorksheetFunction.Countifs(sheets("工作表1") _
.Range("a1:a" & irow)),sheets("工作表1").cells(i,1),sheets("工作表1").range("b1:b") & irow)),sheets("工作表1").cells(i,2))
Next i

What's wrong with above code? It does not work.

idnoidno
06-19-2017, 08:22 AM
Option Explicit
Sub redupli()
Dim rg As Range
Dim arr, brr
Dim i As Long, irow As Long, k As Long
Dim sh As Worksheet
Dim sh1 As Worksheets


arr = Range("a1").CurrentRegion.Value
Worksheets.Add.Name = "temp"
[a1].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
Range("a1:c" & Cells(Rows.Count, 1).End(xlUp).Row) _
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
Set sh = Worksheets("工作表1")
Set sh1 = Worksheets("temp")
sh1.Select
irow = sh1.Cells(Rows.Count, 1).End(xlUp).Row
brr = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = 2 To UBound(brr)
Cells(i, 4) = Application.WorksheetFunction.CountIfs(sh _
.Range("a1:a" & irow), Cells(i, 1), sh.Range("b1:b" & irow), Cells(i, 2))
Next i
End Sub


The second step.But red code is wrong.Why?

mdmackillop
06-19-2017, 09:20 AM
The second step.But red code is wrong.Why?

Dim sh1 As Worksheets


Make sure all ranges are fully referenced to avoid errors

Sub redupli()
Dim rg As Range
Dim arr, brr1, brr2, brr3
Dim i As Long, irow As Long, k As Long
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim F
Dim lRow As Long

Set sh = ActiveSheet
arr = Range("a1").CurrentRegion.Value
Set sh1 = Worksheets.Add
sh1.Name = "temp"
sh1.[a1].Resize(UBound(arr, 1), 2) = arr
sh1.Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row) _
.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

With sh
Set brr1 = sh.Range("a1:a" & .Cells(Rows.Count, 1).End(xlUp).Row)
Set brr2 = brr1.Offset(, 1)
Set brr3 = brr1.Offset(, 2)
End With

Set F = Application.WorksheetFunction
With sh1
lRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lRow
.Cells(i, 3) = F.CountIfs(brr1, .Cells(i, 1), brr2, .Cells(i, 2))
.Cells(i, 4) = F.SumIfs(brr3, brr1, .Cells(i, 1), brr2, .Cells(i, 2))
Next i
End With
End Sub

idnoidno
06-20-2017, 01:48 AM
Mr. mdmackillop,
thank you for your reply, I admire your knowledge in Excel vba, you mentioned that you are through the development of a macro to learn vba, do you not write code experience before?

mdmackillop
06-20-2017, 03:31 AM
I leaerned a little, then how to edit code created by the Macro recorder. My first code was to create a develop a simple database for the company I worked in base mainly on the Nwind access sample. From there I started trying to answer questions and learned from the better answers (still doing that: Mana's code above is a great example). Learn to use Google efficiently; it's all out there waiting to be adapted into the answer to a specific query.
If you want a more formal training structure, I'm not the one to ask.