Sumproduct instead of SumifS in VBA (application.sumproduct)
It appears that Jim is right - I'm also getting the type-mismatch
error.
Perhaps you can work around this by using some tempcell - put the
formula into a cell, get Excel to evalueate it, read the cells value
and then remove the formula from the cel...
Not an elegant solution but...
On Dec 30, 12:58*pm, "Jim Rech" wrote:
I don't think VBA can evaluate this "formula" like Excel itself can. It
breaks down pretty early in the process.
While it can do this:
*Dim Arr As Variant
*Arr = Application.Index(Range("Holdings"), 0, 1)
returning an array of the values in column 1 of Holdings, you would hope
that this:
*Dim Arr As Variant
*Arr = (Application.Index(Range("Holdings"), 0, 1) = "ff")
would return an array of True and False. But it errors. *I think in VBA you
just have to address each element of the array individually.
"Majken Bilslev-Jensen" wrote in message
...
Hi all,
I have a problem in VBA using application.sumproduct.
The following code does not work:
Application.SumProduct((Application.Index(holdings .Range("holdings"),
0, 1) = "ff") * (Application.Index(holdings.Range("holdings"), 0, 5) =
"gg") * Application.Index(holdings.Range("holdings"), 0, 3))
Can anyone help me? Thanks heaps!- Hide quoted text -
- Show quoted text -
|