Count Unique Values with CountIF works in Excel but gives type mismatch error in VBA

Question

So I'm trying to get how many unique categories I have in my data range and I know how to do it in Excel

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

but when I try to use it via VBA i get a type mismatch error.

 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)))

---

Can someone help me? It's driven me insane.

Answer 1

In VBA the worksheet functions do not behave exactly in the same way. You can use Evaluate, writing the formulas as they are and using range addresses. But a better way would be using a Scripting.Dictionary. Please, try the next code, which will return the unique values number, but also which are these unique values and how many per each (in columns "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

To Evaluate the worksheet function, please try the next piece of code:

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