Run-time error '13': Type mismatch on WorksheetFunction.CountIf when the actual Excel Formula (with similar syntax) works perfectly

2
votes

Okay, so in Cell B1 I have the below formula (not an Array formula) which works perfectly in Excel:

SUMPRODUCT(1/COUNTIF(A1:A4,A1:A4))

This identifies unique values in a range of cells. So, if A1 = "A", A2 = "B", A3 = "B" and A4 = "C" the result of this formula is 3. Which is correct. However, if I try and replicate the exact same function in VBA as follows...:

Range("B1") = WorksheetFunction.SumProduct(1 / WorksheetFunction.CountIf(Range("A1:A4"), Range("A1:A4")))

I get a "Run-Time error '13': Type mismatch" error.

I've narrowed it down to the fact that VBA doesn't like that I'm using a multi-cell Range (ie A1:A4) as oppose to a single cell Range (ie A1) in my CountIf.

I've already written procedure to accommodate the end result that I'm looking for but it's not as elegant as it would be if I could just get the above to work. Can anyone enlighten me on what I might be doing wrong or is this simply not possible?

1
vbaexcel
vba doesn;t like SUMPRODUCT. You might have better success with application.evaluate(...) if you properly referen ce the parent worksheets. - user4039065
VBA has no issue at all with SUMPRODUCT. It does have an issue with trying to create arrays with simple operators like / though! Also, you can't use Worksheetfunction.Countif like that - you have to use Application.Countif - Rory

1 Answers

0
votes

This is the closest I have managed to achieve it VBA:

Sub TestMe
    Debug.Print Application.SumProduct(1 / Evaluate("=COUNTIF(A1:A4,A1:A4)"))
End Sub

In the CountIf() formula in Excel you are using an array formula, pressing CTRLSHIFTENTER and it seems that the WorksheetFunction cannot replicate it. However, Evaluate() does it without a problem.