Sumproduct instead of SumifS in VBA (application.sumproduct)
 

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!

Sumproduct instead of SumifS in VBA (application.sumproduct)
 

Try application.WorksheetFunction.SumProduct

On Dec 30, 12:08*pm, Majken Bilslev-Jensen
wrote:
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!

Sumproduct instead of SumifS in VBA (application.sumproduct)
 

Makes no difference....

Sumproduct instead of SumifS in VBA (application.sumproduct)
 

That applies to all the functions you use - also the .index
Did you make that change too?
As far as I know the only way to reference/use genuine Excel Worksheet
functions is via the:
application.worksheetfunction.
construct.
So, did you make that other change too?


On Dec 30, 12:48*pm, Majken Bilslev-Jensen
wrote:
Makes no difference....

Sumproduct instead of SumifS in VBA (application.sumproduct)
 

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!

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 -

Sumproduct instead of SumifS in VBA (application.sumproduct)
 

On Dec 30, 7:12*am, AB wrote:
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 -- Hide quoted text -

- Show quoted text -

With sumproduct you must use application.EVALUATE("your formula")

Sumproduct instead of SumifS in VBA (application.sumproduct)
 

On Dec 30, 4:08*am, Majken Bilslev-Jensen
wrote:
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))

Try this:

Evaluate("SUMPRODUCT((INDEX(Holdings,0,1)="ff")*(I NDEX(Holdings,
0,5)="gg"),
INDEX(Holdings,0,3)")

Note: Enter the entire formula on __one__ VBA line or use apply VBA
line continuation syntax appropriately.