SumProduct in VBA

[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.

 

[Gary''s Student]

two things:

1. make B a string variable
2. splice it into the formula:
................" & B & "..................

 

[Sam Wilson]

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

Should work.

 

[Sam Wilson]

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

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

 

[Jacob Skaria]

Try

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

If this post helps click Yes

 

[Patrick Molloy]

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))")

 

[Dave]

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.

 

[Jacob Skaria]

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

 

[Jacob Skaria]

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

 

[Dave]

Hi Jacob,
Thanks for your help and clarification.
Dave.