sumproduct in VBA
 

I want to use the SUMPRODUCT function in my code. The function, as I would
write it in an Excel cell, is as follows.

=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open") )=0,"Done","Pending Action")

Where "series" and "status" are ranges of cells (both single column, 10,000
rows) that have been named.

I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."

Application.WorksheetFunction.SumProduct((Range("s eries") = Cells(1,
1).Value) * (Range("status") = "Open"))

Application.WorksheetFunction.SumProduct((Array("s eries") = Cells(1,
1).Value) * (Array("status") = "Open"))

I've stopped iterating through other possible variations to save myself time
and frustration. Does anyone know the correct arguments to get the desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open", then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!

sumproduct in VBA
 

Hi

I'd have my code enter the formula into a spare cell, calculate, read &
remember the result and delete the formula.

HTH. Best wishes Harald

"xlcharlie" skrev i melding
...
I want to use the SUMPRODUCT function in my code. The function, as I
would
write it in an Excel cell, is as follows.

=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open") )=0,"Done","Pending
Action")

Where "series" and "status" are ranges of cells (both single column,
10,000
rows) that have been named.

I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."

Application.WorksheetFunction.SumProduct((Range("s eries") = Cells(1,
1).Value) * (Range("status") = "Open"))

Application.WorksheetFunction.SumProduct((Array("s eries") = Cells(1,
1).Value) * (Array("status") = "Open"))

I've stopped iterating through other possible variations to save myself
time
and frustration. Does anyone know the correct arguments to get the
desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open",
then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!

sumproduct in VBA
 

Just evaluate it

myVal = Evaluate("=SUMPRODUCT((series=Sheet1!$A2)*(status= ""Open""))")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xlcharlie" wrote in message
...
I want to use the SUMPRODUCT function in my code. The function, as I
would
write it in an Excel cell, is as follows.

=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open") )=0,"Done","Pending
Action")

Where "series" and "status" are ranges of cells (both single column,
10,000
rows) that have been named.

I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."

Application.WorksheetFunction.SumProduct((Range("s eries") = Cells(1,
1).Value) * (Range("status") = "Open"))

Application.WorksheetFunction.SumProduct((Array("s eries") = Cells(1,
1).Value) * (Array("status") = "Open"))

I've stopped iterating through other possible variations to save myself
time
and frustration. Does anyone know the correct arguments to get the
desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open",
then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!

sumproduct in VBA
 

Works perfectly! Thanks.

"Bob Phillips" wrote:

Just evaluate it

myVal = Evaluate("=SUMPRODUCT((series=Sheet1!$A2)*(status= ""Open""))")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xlcharlie" wrote in message
...
I want to use the SUMPRODUCT function in my code. The function, as I
would
write it in an Excel cell, is as follows.

=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open") )=0,"Done","Pending
Action")

Where "series" and "status" are ranges of cells (both single column,
10,000
rows) that have been named.

I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."

Application.WorksheetFunction.SumProduct((Range("s eries") = Cells(1,
1).Value) * (Range("status") = "Open"))

Application.WorksheetFunction.SumProduct((Array("s eries") = Cells(1,
1).Value) * (Array("status") = "Open"))

I've stopped iterating through other possible variations to save myself
time
and frustration. Does anyone know the correct arguments to get the
desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open",
then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!

sumproduct in VBA
 

Thanks Harald. I was aware of that option, but looking for a way to do it in
code alone. Turns out, the evaluate function allows you to do what you were
suggesting, but in code alone:

myVal = Evaluate("=SUMPRODUCT((series=Sheet1!$A2)*(status= ""Open""))")

Thanks again for your help.

"Harald Staff" wrote:

Hi

I'd have my code enter the formula into a spare cell, calculate, read &
remember the result and delete the formula.

HTH. Best wishes Harald

"xlcharlie" skrev i melding
...
I want to use the SUMPRODUCT function in my code. The function, as I
would
write it in an Excel cell, is as follows.

=IF(SUMPRODUCT((series=Sheet1!$A2)*(status="Open") )=0,"Done","Pending
Action")

Where "series" and "status" are ranges of cells (both single column,
10,000
rows) that have been named.

I've tried using the follow variations of VB code, but I keep getting a
run-time error '13' message: "Type mismatch."

Application.WorksheetFunction.SumProduct((Range("s eries") = Cells(1,
1).Value) * (Range("status") = "Open"))

Application.WorksheetFunction.SumProduct((Array("s eries") = Cells(1,
1).Value) * (Array("status") = "Open"))

I've stopped iterating through other possible variations to save myself
time
and frustration. Does anyone know the correct arguments to get the
desired
result? My aim is to look at a large tabular dataset, determine if the
"status" of any items corresponding to a specified identifier is "open",
then
execute other code accordingly. So if sumproduct = 0 do x, else do y.
Thanks in advance for your help!