[AccessD] OT: Excel Application.WorksheetFunction

Hi group,

 

In an Excel app I want to look up a value for two conditions using Application.WorksheetFunction.SumProduct

Say I have this in a worksheet:

                      A                    B                    C

1                    Lookup matrix:

2                    100                 2                    20

3                    200                 3                    30

4                    500                 2                    50

5                    500                 4                    80

6

7                    Input for lookup:                     

8                    500                 2

9

10                  Result:

11                  50                  

 

I could get the result for cell A11 typing in this formula for cell A11:

=SUMPRODUCT(($A$2:$A$5=A8)*($B$2:$B$5=B8)*$C$2:$C$5)

 

Now in my app I don't want formulas but only plain values because the sheet contains huge amounts of data and formulas would hurt performance.

And I don't want to copy a formula and then paste it as value because this too would hurt performance.

So I would like to use Application.WorksheetFunction.SumProduct in VBA to get the lookup values and then insert the values into the sheet.

I have done this successfully using Application.WorksheetFunction.VLookup in VBA to fill an array of cells feeding the VLookup-function with input-arrays.

But Application.WorksheetFunction.SumProduct is troubling me.

Somehow it don’t accept the same kind of syntax as Application.WorksheetFunction.VLookup does and I keep getting runtime errors telling "type mismatch".

 

Any suggestion appreciated

Asger