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

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 more:

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

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 more:
>
> 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
>