v = Activesheet.Evaluate("SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15)")


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tetsuya Oguma" wrote in message
...
Hi all,

I have got this groovy formula below as an Excel formula and would like to
make it usable as VBA:

=SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15)

I realised this "-" in the formula means "multiply by -1".

I tried:

Dim v As Variant
v = Application.WorksheetFunction.SumProduct(--(Range("B3:b16") -
Range("b2:b15") = 0), Range("B3:b16") - Range("b2:b15"))

But I get Type Mismatch error...

Can anyone help?

Thanks in advance,
Tetsuya