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!

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!

Makes no difference....

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....

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!

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 -

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")

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.