Will this do?

Total = 0
for each cell in Range("B3:B16")
if cell = cell.offset(-1,0) then
total = total + (cell - cell.offset(-1,0))
end if
next cell

The "--" really converts the TRUE's to 1 and the False's to 0. so instead
of getting an array of

{true, false, false, True,...)

You get

{1,0,0,1,...}

Sumproduct doesn't know how to multiply {true * 1}. the -- changes this to
{1 * 1}

"Tetsuya Oguma" wrote:

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