Excel VBA + SumProduct + not working!

  • Thread starter Thread starter DavidMoeller
  • Start date Start date
D

DavidMoeller

Gents-
I am performing a basic SumProduct, but am having problems when no
just directly pasting the formula in the cell. I've looked at som
past posts, and that's where I got the "Application.Evaluate" hint, bu
can't make it work.

I am trying to use the following code:

TTTT = Application.Evaluate("SumProduct( _
(Sheets(varInput).Range(C2:C3940) = ""N"") _
* (Sheets(varInput).Range("B" & 2 & ":B" & TR) < 38352) _
* (Sheets("8 6 04output2").Range("G2:G3940"))")

I have tried different variations of parenthesis and quotation marks
but can't get it to work. I am admittedly new at this.

The following works for me, but I think it would be more efficient if
had excel do the calculation and just insert the result in the cell.

Sheets(AMname).Cells(5, 6) = "=SUMPRODUCT(('" & varInput & "'!R2C1:R[
& TR & "]C1=" & AMLname & ")*('" & varInput & "'!R2C3:R[" & TR
"]C3=""N"")*('" & varInput & "'!R2C2:R[" & TR & "]C2<=38352)*('"
varInput & "'!R2C2:R[" & TR & "]C2>38256)*('" & varInput & "'!R2C7:R[
& TR & "]C7))
 
Hi
try:
TTTT = Application.Evaluate("=SumProduct( _
'varInput'!C2:C3940 = ""N"") * ('varInput'!B2:B" & TR & ") < 38352) _
* ('8 6 04output2'!G2:G3940))")

But you know this will work only if TR = 3940
 
Back
Top