Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct instead of SumifS in VBA (application.sumproduct)
Makes no difference....
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 -- SUMIFS v SUMPRODUCT | Excel Worksheet Functions | |||
Are SUMIFS and SUMPRODUCT the same? | Excel Discussion (Misc queries) | |||
Application.Worksheetfunction.Sumproduct | Excel Programming | |||
application.sumproduct help please | Excel Programming | |||
application.sumproduct help please | Excel Programming |