Can't transfer SUMPRODUCT function to VBA

[Julesdude]

Hello all,

I'm having difficulty transfering a worksheet function into VBA, assigning the result to a variable.

The function is

=SUMPRODUCT(1/COUNTIF(A3:A8,A3:A8))

How would I translate that into VBA. I keep getting type mismatches each time I assign it to the variable. Of course, I am using the application.worksheetfunction. etc prefix before doing this but to no avail.

Please can someone help. Thanks.

 

[VoG]

Maybe like this

x = WorksheetFunction.SumProduct(1 / CountIf(Range("A3:A8"), Range("A3:A8")))

 

[Joe4]

Peter,

It doesn't like that either, as it says COUNTIF is an undefined Sub/Function.

So I tried a slight variation of yours:

    x = WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(Range("A3:A8"), Range("A3:A8")))

and that still returns a mismatch error.

I don't think VBA likes COUNTIF having a multi-cell range for its second argument, as this returns a mismatch error:

x = WorksheetFunction.CountIf(Range("A3:A8"), Range("A3:A8"))

but this does not:

x = WorksheetFunction.CountIf(Range("A3:A8"), Range("A3:))

Not really sure I understand what:
COUNTIF(A3:A8,A3:A8)
is supposed to accomplish anyway.

Might need to take a step back here and ask what the OP is trying to get with this formula.

 

[cb12]

Not really sure I understand what:
COUNTIF(A3:A8,A3:A8)
is supposed to accomplish anyway.

I'm not sure how to incorporate the multi-range countif either, but the formula is a way to count distinct values in a range without using an array.

 

[VoG]

Hi Joe

I didn't really think this through and I wonder whether the formula is an array formula. In any case maybe the solution would be to write the formula to some remote cell (IV65536) then read the result into a variable.

 

[VoG]

... like this

With Range("IV65336")
    .Formula = "=SUMPRODUCT(1/COUNTIF(A3:A8,A3:A8))"
    x = .Value
    .ClearContents
End With

 

[Julesdude]

Thanks for your help so far guys. When added to a cell, the formula in its original form gives me the number of unique occurrences in a range.

I want to Dim and assign an array based on number of unique occurrences and fill each array with the value in the cell. This will then populate a listbox on my userform.
Hope that makes sense. Anything else that can be done to make this work?

 

[VoG]

Then maybe work with an array directly

Sub ctest()
Dim i As Long, j As Long, k As Long, x()
ReDim x(1)
x(0) = Range("A3").Value
For i = 4 To 8
    If IsError(Application.Match(Range("A" & i).Value, x, 0)) Then
        j = j + 1
        ReDim Preserve x(j)
        x(j) = Range("A" & i).Value
    End If
Next i
For k = LBound(x) To UBound(x)
    MsgBox x(k)
Next k
End Sub

 

[pgc01]

Hi

Another option, assuming the cells in the range are not empty:

Sub Test()
Dim v As Variant
Dim r As Range
 
Set r = Range("A3:A8")
 
With Application
    v = .Sum(.Power(.CountIf(r, r), -1))
End With
 
End Sub

 

[pgc01]

Jules

Remark: I just posted the syntax to answer the original question. To count the unique values I'd probably use a dictionary and not worksheet functions.