| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| SumProduct | Excel Worksheet Functions | |||
| sumproduct help | Excel Worksheet Functions | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Hybrid Mode
