Quick Question on Calc to Long Var

[Rudi]

What am I missing??
It keeps on saying Object Required?

Code: Select all

Dim lgrp As Long 
    lgrp = WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(rData.Columns(1).Address, rData.Columns(1).Address))

[HansV]

The first argument to CountIf should be a range, not an address. The second argument should be a value to compare the range to.
In a worksheet function saved as an array function, you can supply a range as second argument. This won't work in VBA however - you can't evaluate array formulas using WorksheetFunction.
As an alternative, you can use Evaluate:

Code: Select all

    lgrp = Evaluate("=SUMPRODUCT(1/COUNTIF(" & rData.Columns(1).Address & "," & rData.Columns(1).Address & "))")

[Rudi]

TX...That is working well now.
Again this Evaluate Function. I learn it...then never use it...then forget about it...until I need it...and then I dunno how to use it again...
Life is so unfair sometimes!!

TX Hans.