vba - Excel VBA 计算和打印不同值

Question

我必须计算一列中不同值的数量，并将其与不同值一起打印并在另一张纸中进行计数。我正在使用这段代码，但由于某种原因，它没有返回任何结果。谁能告诉我哪里遗漏了这部分!

Dim rngData As Range
Dim rngCell As Range
Dim colWords As Collection
Dim vntWord As Variant
Dim Sh As Worksheet
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet

On Error Resume Next

Set Sh1 = Worksheets("A")
Set Sh2 = Worksheets("B")
Set Sh3 = Worksheets("C")

Sh1.Range("A2:B650000").Delete

Set Sh = Worksheets("A")
Set r = Sh.AutoFilter.Range
r.AutoFilter Field:=24
r.AutoFilter Field:=24, Criteria1:="My Criteria"

Sh1.Range("A2:B650000").Delete

Set colWords = New Collection

Dim lRow1 As Long
lRow1 = <some number>

Set rngData = <desired range>
For Each rngCell In rngData.Cells
    colWords.Add colWords.Count + 1, rngCell.Value
    With Sh1.Cells(1 + colWords(rngCell.Value), 1)
        .Value = rngCell.Value
        .Offset(0, 1) = .Offset(0, 1) + 1
    End With
Next

上面是我的完整代码。我所需的结果很简单，计算列中每个单元格的出现次数，并将其打印在另一张纸上并包含出现次数。谢谢!

谢谢!导航。

Answer 1

使用字典对象来做到这一点非常简单和实用。逻辑与 Kittoes 的答案类似，但字典对象更快、更高效，并且您可以输出所有键和项目的数组，这正是您想要在此处执行的操作。我已简化代码以从 A 列生成列表，但您会明白的。

Sub UniqueReport()

Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant

varray = Range("A1:A10").Value

'Generate unique list and count
For Each element In varray
    If dict.exists(element) Then
        dict.Item(element) = dict.Item(element) + 1
    Else
        dict.Add element, 1
    End If
Next

'Paste report somewhere
Sheet2.Range("A1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.keys)
Sheet2.Range("B1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.items)

End Sub

工作原理:您只需将范围转储到变量数组中即可快速循环，然后将每个值添加到字典中。如果存在，您只需取出与它们的键对应的项目(从 1 开始)并向其添加 1。然后最后只需将唯一列表和计数放在您需要的地方即可。请注意，我为字典创建对象的方式允许任何人使用它 - 无需添加对您的代码的引用。