ExcelBanter - SumProduct in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - SumProduct in VBA (https://www.excelbanter.com/excel-programming/433796-sumproduct-vba.html)

Dave

SumProduct in VBA

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

SumProduct in VBA

two things:

1. make B a string variable
2. splice it into the formula:
................" & B & "..................
--
Gary''s Student - gsnu200904

"Dave" wrote:

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.

Sam Wilson

SumProduct in VBA

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

Should work.

"Dave" wrote:

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.

Sam Wilson

SumProduct in VBA

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

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

"Dave" wrote:

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.

Jacob Skaria

SumProduct in VBA

Try

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

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

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.

Patrick Molloy[_2_]

SumProduct in VBA

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" wrote:

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.

Dave

SumProduct in VBA

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.

"Sam Wilson" wrote:

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

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

"Dave" wrote:

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.

Jacob Skaria

SumProduct in VBA

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

"Dave" wrote:

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.

"Sam Wilson" wrote:

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

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

"Dave" wrote:

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.

Jacob Skaria

SumProduct in VBA

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
---------------
Jacob Skaria

"Jacob Skaria" wrote:

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

"Dave" wrote:

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.

"Sam Wilson" wrote:

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

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

"Dave" wrote:

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.

Dave

SumProduct in VBA

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"Jacob Skaria" wrote:

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

"Dave" wrote:

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.

"Sam Wilson" wrote:

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

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

"Dave" wrote:

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.

All times are GMT +1. The time now is 06:54 AM.