Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can i use sumproduct this way in VB?
Can i use sumproduct in VB the same way in a cell of excel. The only
difference being i am using application.worksheetfunction.sumproduct(--(Worksheets("A").Range("A2:A9") _ =Worksheets.Range("A1),--(Worksheets("A").Range("b2:b9") _ =Worksheets.Range("b1),--(Worksheets("A").Range("c2:c9")) Is that possible, or do i need to use a different method to accomplish the same task? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can i use sumproduct this way in VB?
You've got a few typos in your code--"a1 and "b1 are missing ending quotes. And
each of those cells is missing the worksheet name: Worksheets.Range("A1) You can use application.evaluate to do what you want. I like this style: Option Explicit Sub testme02() Dim RngA As Range Dim RngB As Range Dim RngC As Range Dim CellA As Range Dim CellB As Range Dim myFormula As String With Worksheets("a") Set RngA = .Range("a2:A9") Set RngB = .Range("b2:b9") Set RngC = .Range("c2:c9") Set CellA = .Range("a1") Set CellB = .Range("B1") End With myFormula = "sumproduct(--(" & RngA.Address(external:=True) _ & "=" & CellA.Address(external:=True) & ")," _ & "--(" & RngB.Address(external:=True) _ & "=" & CellB.Address(external:=True) & ")," _ & "(" & RngC.Address(external:=True) & "))" Debug.Print myFormula 'nice for testing! MsgBox Application.Evaluate(myFormula) End Sub Andy wrote: Can i use sumproduct in VB the same way in a cell of excel. The only difference being i am using application.worksheetfunction.sumproduct(--(Worksheets("A").Range("A2:A9") _ =Worksheets.Range("A1),--(Worksheets("A").Range("b2:b9") _ =Worksheets.Range("b1),--(Worksheets("A").Range("c2:c9")) Is that possible, or do i need to use a different method to accomplish the same task? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can i use sumproduct this way in VB?
Andy,
Can't quite work out your code, but I wrote this little function that works: Function a() a = Application.WorksheetFunction.SumProduct( _ Worksheets("A").Range("A2:A9"), _ Worksheets("A").Range("b2:b9"), _ Worksheets("A").Range("c2:c9")) End Function if you are trying to do the sumproduct of the above three ranges. -- HTHs Martin "Andy" wrote: Can i use sumproduct in VB the same way in a cell of excel. The only difference being i am using application.worksheetfunction.sumproduct(--(Worksheets("A").Range("A2:A9") _ =Worksheets.Range("A1),--(Worksheets("A").Range("b2:b9") _ =Worksheets.Range("b1),--(Worksheets("A").Range("c2:c9")) Is that possible, or do i need to use a different method to accomplish the same task? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
can i use sumproduct this way in VB?
Is there anyway to put that into a function script that always updates itself?
"Dave Peterson" wrote: You've got a few typos in your code--"a1 and "b1 are missing ending quotes. And each of those cells is missing the worksheet name: Worksheets.Range("A1) You can use application.evaluate to do what you want. I like this style: Option Explicit Sub testme02() Dim RngA As Range Dim RngB As Range Dim RngC As Range Dim CellA As Range Dim CellB As Range Dim myFormula As String With Worksheets("a") Set RngA = .Range("a2:A9") Set RngB = .Range("b2:b9") Set RngC = .Range("c2:c9") Set CellA = .Range("a1") Set CellB = .Range("B1") End With myFormula = "sumproduct(--(" & RngA.Address(external:=True) _ & "=" & CellA.Address(external:=True) & ")," _ & "--(" & RngB.Address(external:=True) _ & "=" & CellB.Address(external:=True) & ")," _ & "(" & RngC.Address(external:=True) & "))" Debug.Print myFormula 'nice for testing! MsgBox Application.Evaluate(myFormula) End Sub Andy wrote: Can i use sumproduct in VB the same way in a cell of excel. The only difference being i am using application.worksheetfunction.sumproduct(--(Worksheets("A").Range("A2:A9") _ =Worksheets.Range("A1),--(Worksheets("A").Range("b2:b9") _ =Worksheets.Range("b1),--(Worksheets("A").Range("c2:c9")) Is that possible, or do i need to use a different method to accomplish the same task? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
can i use sumproduct this way in VB?
Maybe...
What do you mean? But if you're writing a VBA function to replace the =sumproduct() worksheet function, you'll find that the =sumproduct() worksheet function that's built into excel is much, much faster. Andy wrote: Is there anyway to put that into a function script that always updates itself? "Dave Peterson" wrote: You've got a few typos in your code--"a1 and "b1 are missing ending quotes. And each of those cells is missing the worksheet name: Worksheets.Range("A1) You can use application.evaluate to do what you want. I like this style: Option Explicit Sub testme02() Dim RngA As Range Dim RngB As Range Dim RngC As Range Dim CellA As Range Dim CellB As Range Dim myFormula As String With Worksheets("a") Set RngA = .Range("a2:A9") Set RngB = .Range("b2:b9") Set RngC = .Range("c2:c9") Set CellA = .Range("a1") Set CellB = .Range("B1") End With myFormula = "sumproduct(--(" & RngA.Address(external:=True) _ & "=" & CellA.Address(external:=True) & ")," _ & "--(" & RngB.Address(external:=True) _ & "=" & CellB.Address(external:=True) & ")," _ & "(" & RngC.Address(external:=True) & "))" Debug.Print myFormula 'nice for testing! MsgBox Application.Evaluate(myFormula) End Sub Andy wrote: Can i use sumproduct in VB the same way in a cell of excel. The only difference being i am using application.worksheetfunction.sumproduct(--(Worksheets("A").Range("A2:A9") _ =Worksheets.Range("A1),--(Worksheets("A").Range("b2:b9") _ =Worksheets.Range("b1),--(Worksheets("A").Range("c2:c9")) Is that possible, or do i need to use a different method to accomplish the same task? -- Dave Peterson -- Dave Peterson |
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) | |||
Help with SUMPRODUCT | Excel Worksheet Functions | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) |