Excel VBA计数和打印不同的值 [英] Excel VBA to count and print distinct values

问题描述

我必须从列中计算不同值的数量，并使用不同的值打印，并在另一个工作表中计数。我正在使用这段代码，但由于某种原因，它没有返回任何结果。任何人都可以告诉我我在哪里错过了这件作品！

I have to count number of distinct values from a column and print it with the distinct value and count in another sheet. I am working with this piece of code, but for some reason, it is not returning any result. Could anyone tell me where I am missing the piece!

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

以上是我的完整代码..我需要的结果很简单，计算列中每个单元格的出现次数，并将其打印在另一个表格中事件计数。谢谢！

Above is my full code.. My required outcome is simple, count number of occurrences of each cell in a column, and print it in another sheet with the count of occurrences. Thanks!

谢谢！
导航。

Thanks! Navs.

推荐答案

使用字典对象，这是非常简单实用的。逻辑类似于Kittoes的答案，但字典对象要快得多，效率高，您可以输出所有键和项目的数组，您要在此处执行。我已经简化了从A列生成列表的代码，但你会得到这个想法。

This is extreamlly easy and practical to do using a dictionary object. The logic is similar to Kittoes answer, but the dictionary object is much faster, effecient, and you can output an array of all keys and items, which you want to do here. I have simplified the code to generating a list from column A, but you will get the idea.

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开始），并添加一个。然后在最后只需拍摄独特的列表和计数，无论你需要什么。请注意，我创建字典对象的方式允许任何人使用它 - 无需添加对代码的引用。

How it works: You just dump the range into a variant array to loop through quickly, then add each to the dictionary. If it exists, you just take the item that goes with they key (starts at 1) and add one to it. Then at the end just slap the unique list and the counts wherever you need them. Please note that the way I create an object for the dictionary allows anyone to use it - there is no need to add a reference to your code.

这篇关于Excel VBA计数和打印不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！