Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUMPRODUCT() Question | Excel Discussion (Misc queries) | |||
SUMPRODUCT Question | Excel Discussion (Misc queries) | |||
SUMPRODUCT Question | Excel Discussion (Misc queries) | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
another sumproduct question | Excel Worksheet Functions |