Thread: VBA match 2 criteria and return a value

Hi,

I have a feeling I need to do some sort of sumproduct, like application.sumproduct but..

I have tried doing a simple sum product for this in excel and it isn't working.

The reason it needs to be in VBA is because it calculates this as part of a change event and also it would be nice to learn this function within VBA.

I need to match the value of the cell with name DTYPE to the range STYPE, I also need to match the value of cell with name DABS to the range ABSD then I need the value returning from range ABSC.

When I tried this in excel it returned a value of 0, using this formula:

=SUMPRODUCT((STYPE=DTYPE)*(ABSD=DABS),(ABSC))

I have tried this function in VBA but to absolutely no result worth displaying.

Any ideas?

Andy

In terms of conducting SUMPRODUCT in VBA

Dim dblAns As Double
dblAns = Evaluate("SUMPRODUCT((STYPE=DTYPE)*(ABSD=DABS),ABSC)")

others might suggest using [ ] notation for the Evaluate
(above assumes that code resides within the Sheet Object in VBE)

note: the SUMPRODUCT itself assumes that ABSC has the same height and width as the result of STYPE*ABSD