In contrast, the immediate window indicates it works fine for me a posted:

Set tarrng = Range("A1:A10")
prova = 5

? ("=SUMPRODUCT((" & _
tarrng.Address & "=" & ProVa & ")*(" & _
tarrng.Address & "<0))")

=SUMPRODUCT(($A$1:$A$10=5)*($A$1:$A$10<0))

? Evaluate("=SUMPRODUCT((" & _
tarrng.Address & "=" & ProVa & ")*(" & _
tarrng.Address & "<0))")

0

Assumes ProVa is a number. If it is a string

Set tarrng = Range("A1:A10")
prova = "Dog"

? ("=SUMPRODUCT((" & _
tarrng.Address & "=""" & ProVa & """)*(" & _
tarrng.Address & "<0))")
=SUMPRODUCT(($A$1:$A$10="Dog")*($A$1:$A$10<0))

? Evaluate("=SUMPRODUCT((" & _
tarrng.Address & "="" & ProVa & "")*(" & _
tarrng.Address & "<0))")
0

--
Regards,
Tom Ogilvy


"Shawn" wrote in message
...
Same error. I tried double quotes too "" & & "" and "" "" and triple
quotes and no quotes. ???


--
Thanks
Shawn


"Tom Ogilvy" wrote:

BufYes.Value = WSDSD.Evaluate("=SUMPRODUCT((" & _
tarrng.Address & "=" & ProVa & ")*(" & _
tarrng.Address & "<0))")

--
Regards,
Tom Ogilvy


"Shawn" wrote in message
...
Below is an excert of code:

Dim rng As Range
Dim rng2 As Range
Dim tarrng As Range

Set rng = ActiveCell
Set rng2 = ActiveCell.End(xlDown)
Set tarrng = Range(rng, rng2)

tarrng.Select


BufYes.Value = WSDSD.Evaluate("=SUMPRODUCT((tarrng=" & ProVa &
")*(tarrng<0)")


BufYes is returning a #value error. Variations of this code works. I
know
the current problem rests in the tarrng variable but I am not sure
why.
???
--
Thanks
Shawn