SumProduct in VBA

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,
XL2003
I have the following Code:

Do Until Sheets("Sheet2").Cells(A, 4) = ""

B = Sheets("Sheet2").Cells(A, 4)

Sheets("Sheet2").Cells(A, 6) =
Evaluate("=SUMPRODUCT(--(Sheet1!C3:C100 = B) * (Sheet1!E3:E100))")

A = A + 1

Loop

In the Evaluate("Sumproduct... line, B is a variable, but it's not being read.
Can I use a variable in the above line?
Thanks in advance.
Dave.
 
two things:

1. make B a string variable
2. splice it into the formula:
................" & B & "..................
 
Evaluate("=SUMPRODUCT(--(Sheet1!C3:C100 = " & B & ") * (Sheet1!E3:E100))")

Should work.
 
Me again, if B is text rather than a number, you'll need:

Evaluate("=SUMPRODUCT(--(Sheet1!C3:C100 = """ & B & """) * (Sheet1!E3:E100))")
 
Try

Sheets("Sheet2").Cells(a, 6) = _
Evaluate("=SUMPRODUCT(--(Sheet1!C3:C100=" & B & ")*(Sheet1!E3:E100))")

If this post helps click Yes
 
in yuor code 'b' is just a letter

either

Sheets("Sheet2").Cells(A, 6).FormulaR1C1 = _
"=SUMPRODUCT((Sheet1!R3C3:R100C3 = RC4) * (Sheet1!R3C5:R100C5))"
then
Sheets("Sheet2").Cells(A, 6).Value = Sheets("Sheet2").Cells(A, 6).Value


or this (untried)


With Worksheets("Sheet1")
Sheets("Sheet2").Cells(A, 6).Value =
WorksheetFunction.SumProduct(.Range("C3:C100") = B) * (.Range("E3:E100"))
End With

or Gary's idea


Sheets("Sheet2").Cells(A, 6) = worksheetFunction.SUMPRODUCT((Sheet1!C3:C100
= B) * (Sheet1!E3:E100))")
 
Hi Sam,
B is text, and your code works. Thanks a lot!
Can you tell me why we need 3 lots of double quotes?
Thanks to all the others that responded.
Regards - Dave.
 
Dave, it looks strange but VBA interprets a double-double quote in a string
as a single double quote or CHR(34)

If this post helps click Yes
 
To avoid that confusion you can try out the below..

(Should have bee in one line. Just to show the individual pieces)

"=SUMPRODUCT(--(Sheet1!C3:C100=" &
Chr(34) & B & Chr(34) &
")*(Sheet1!E3:E100))"

If this post helps click Yes
 
Back
Top