计数唯一值与CountIF在Excel中工作，但在VBA中给出类型不匹配错误

Question

我想知道我的数据范围内有多少个唯一的类别我知道如何在Excel

中做到这一点

=SUMPRODUCT(1/COUNTIF(général!N2:N229;général!N2:N229))

但是当我试图通过VBA使用它时，我得到一个类型不匹配的错误。

WorksheetFunction.SumProduct(1 / Application.WorksheetFunction.CountIf(Range("N2:N229"), Range("N2:N229")))

---

x4 = Worksheets("général").Cells(Rows.Count, 14).End(xlUp).Row

"x4 = 229

WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(Range("N2:N"& x4), Range("N2:N" & x4)))

---

WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(Worksheets("général").Range("N2:N" & x4), Worksheets("général").Range("N2:N" & x4)))

---

有人能帮帮我吗?我都快疯了。

Answer 1

在VBA中，工作表函数的行为并不完全相同。您可以使用Evaluate，按原样编写公式并使用范围地址。但更好的方法是使用Scripting.Dictionary。请尝试下一个代码，它将返回唯一值的编号，但也会返回这些唯一值是哪些以及每个唯一值的数量(在"O:P"列中):

Sub countUnique()
Dim sh As Worksheet, lastR As Long, arr, i As Long, dict As Object

Set sh = ActiveSheet
lastR = sh.Range("N" & sh.rows.count).End(xlUp).row
arr = sh.Range("N2:N" & lastR).value 'place the range in an array for  faster processing
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = TextCompare
For i = 1 To UBound(arr)
dict(arr(i, 1)) = dict(arr(i, 1)) + 1
Next i

Debug.Print dict.count & " unique values"
sh.Range("O2").Resize(dict.count, 1).value = Application.Transpose(dict.Keys)
sh.Range("P2").Resize(dict.count, 1).value = Application.Transpose(dict.items)
End Sub

要Evaluate工作表功能，请尝试下一段代码:

Sub CountUniqueEvaluate()
Dim sh As Worksheet, lastR As Long, rng As Range

Set sh = ActiveSheet
lastR = sh.Range("N" & sh.rows.count).End(xlUp).row
Set rng = sh.Range("N2:N" & lastR)

Debug.Print Evaluate("=SUMPRODUCT(1/COUNTIF(" & rng.Address & ", " & rng.Address & "))")
End Sub