Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT statement only works sometimes
I'm writing a long program to analyze my lab's financial data.
I created a subprogram that I want to copy into the longer program. The subprogram calculates an array of subtotals using the SUMPRODUCT command total = ActiveSheet.Evaluate("SUMPRODUCT((" & myRng1.Address & _ "=""" & myVar1 & """)" & "*" & myRng2.Address & ")") It works fine, but I need to port this into the long program. I copy/pasted the subprogram into my main program, but it hangs up at this command. I've used debug.print to confirm all the variables and ranges in the SUMPRODUCT statement, and the active sheet is the same. I'm stuck. What else could be causing this statement to hang up? -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT statement only works sometimes
What are the addresses of myRng1 and myrng2?
What's in myVar1? Do you have any non-numeric data in myrng2? Do you have any errors in myRng1? Just as an aside, I like to specify my ranges a little mo Option Explicit Sub testme() Dim Total As Double Dim myRng1 As Range Dim myRng2 As Range Dim myVar1 As String With ActiveSheet Set myRng1 = .Range("a1:a10") Set myRng2 = .Range("b1:b10") myVar1 = .Range("c1").Value End With Total = Application.Evaluate("SUMPRODUCT((" & myRng1.Address(external:=True) _ & "=""" & myVar1 & """)" & "*" & myRng2.Address(external:=True) & ")") MsgBox Total End Sub Just in case myRng1 and myRng2 aren't on the activesheet. (If you really meant to use the addresses no matter where the ranges were located, then ignore this aside.) Richard wrote: I'm writing a long program to analyze my lab's financial data. I created a subprogram that I want to copy into the longer program. The subprogram calculates an array of subtotals using the SUMPRODUCT command total = ActiveSheet.Evaluate("SUMPRODUCT((" & myRng1.Address & _ "=""" & myVar1 & """)" & "*" & myRng2.Address & ")") It works fine, but I need to port this into the long program. I copy/pasted the subprogram into my main program, but it hangs up at this command. I've used debug.print to confirm all the variables and ranges in the SUMPRODUCT statement, and the active sheet is the same. I'm stuck. What else could be causing this statement to hang up? -- Richard -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT statement only works sometimes
Thanks Dave.
Turns out that I already used "total" for another part of the program, and the data types were in conflict. I guess I need to stay away from such general named variables. -- Richard "Dave Peterson" wrote: What are the addresses of myRng1 and myrng2? What's in myVar1? Do you have any non-numeric data in myrng2? Do you have any errors in myRng1? Just as an aside, I like to specify my ranges a little mo Option Explicit Sub testme() Dim Total As Double Dim myRng1 As Range Dim myRng2 As Range Dim myVar1 As String With ActiveSheet Set myRng1 = .Range("a1:a10") Set myRng2 = .Range("b1:b10") myVar1 = .Range("c1").Value End With Total = Application.Evaluate("SUMPRODUCT((" & myRng1.Address(external:=True) _ & "=""" & myVar1 & """)" & "*" & myRng2.Address(external:=True) & ")") MsgBox Total End Sub Just in case myRng1 and myRng2 aren't on the activesheet. (If you really meant to use the addresses no matter where the ranges were located, then ignore this aside.) Richard wrote: I'm writing a long program to analyze my lab's financial data. I created a subprogram that I want to copy into the longer program. The subprogram calculates an array of subtotals using the SUMPRODUCT command total = ActiveSheet.Evaluate("SUMPRODUCT((" & myRng1.Address & _ "=""" & myVar1 & """)" & "*" & myRng2.Address & ")") It works fine, but I need to port this into the long program. I copy/pasted the subprogram into my main program, but it hangs up at this command. I've used debug.print to confirm all the variables and ranges in the SUMPRODUCT statement, and the active sheet is the same. I'm stuck. What else could be causing this statement to hang up? -- Richard -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct not works | Excel Worksheet Functions | |||
Sumproduct not works | Excel Worksheet Functions | |||
Countif works, Sumproduct doesn't | Excel Worksheet Functions | |||
SUMPRODUCT Works Sometimes Why | Excel Discussion (Misc queries) | |||
Statement works in one scenario, not in other | Excel Programming |