One more scripting.dictionary question

[idnoidno]

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

[Bob Phillips]

And the question is?

[idnoidno]

And the question is?
The data is left, and the result is right.

[mdmackillop]

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]

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]

I'd use "Remove Duplicates", "Countifs" and "SumIfs"

[idnoidno]

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]

You can also use pivot table.

[idnoidno]

You can also use pivot table.
If possible I would like to learn dictionary method from you,wouldn't you?

[mana]

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]

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]

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]

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]

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]

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]

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]

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.