Type Mismatch Using SumProduct

[the]

Hello,


Let's say rng1 is some range like A1:A10, and rng2 is some range like B1:B10.


I am trying to do the Application.WorksheetFunction.Sumproduct and it is giving me a type mismatch. I have:


Application.WorksheetFunction.SumProduct(--(rng1 > 0), rng1, rng2)


I only want to sumproduct positive numbers in rng1. This works in the regular spreadsheet but it's not working in VBA. Seems like the --(rng1 >0) is messing it up but not sure why...


Thanks!

 

[DanteAmor]

try

Sub test3()
  Dim res As Variant
  res = [SUM(if(rng_1>0,rng_1*rng_2))]
End Sub

 

[the]

I still am getting the same error. I have in one worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set rng2 = Target
Call sub1


End Sub

I have a general module:

Option Explicit

Public rng2 As Range, rng1 As Range


Sub sub1()


Set rng1= rng2 .Offset(0, -2)


Dim test As Variant
test = [SUM(if(rng1> 0, rng1* rng2 ))]




End Sub

 

[DanteAmor]

That is not what I had in mind.
What I assumed is the following. (Just for you to test my code), do the following:
- Select cells A1:A10, in the Name Box type rng_1
- Select C1:C10, in the Name Box type rng_2.
Example


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:38.02px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">-4</td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">-5</td><td > </td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">9</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">12</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">30</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">-7</td><td > </td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">7</td><td style="text-align:right; ">42</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">4</td><td > </td><td style="text-align:right; ">8</td><td style="text-align:right; ">32</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">9</td><td style="text-align:right; ">27</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">0</td><td > </td><td style="text-align:right; ">10</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td style="text-align:right; ">152</td><td style="text-align:right; ">152</td><td style="text-align:right; ">152</td><td style="text-align:right; ">152</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D3</td><td >=A3*C3</td></tr><tr><td >D4</td><td >=A4*C4</td></tr><tr><td >D5</td><td >=A5*C5</td></tr><tr><td >D7</td><td >=A7*C7</td></tr><tr><td >D8</td><td >=A8*C8</td></tr><tr><td >D9</td><td >=A9*C9</td></tr><tr><td >D11</td><td >=SUM(D1:D10)</td></tr><tr><td >E11</td><td >=SUMPRODUCT(--(rng_1>0),rng_1,rng_2)</td></tr><tr><td >F11</td><td >{=SUM(IF(rng_1>0,rng_1*rng_2))}</td></tr><tr><td >G11</td><td >=SUMPRODUCT((rng_1>0)*(rng_1*rng_2))</td></tr></table></td></tr></table>



Formulas from D3 to D9 multiply the numbers greater than 0. And in cell D11 we have the sum.
The formulas G11 and E11 are the same.
Formula F11 is array formula. (Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself)

-----------------
Now, If you run this code the result is 152

Sub test3()
  Dim res As Variant
  res = [SUM(if(rng_1>0,rng_1*rng_2))]
End Sub

------------------

And now, For your code to work, it must be like this:

Option Explicit
Public rng2 As Range, rng1 As Range


Sub sub1()
  Set rng1 = rng2.Offset(0, -2)
  Dim test As Variant
  test = Evaluate("=SUM(if(" & rng1.Address & "> 0," & rng1.Address & "*" & rng2.Address & "))")
End Sub

Select cells C1 to C10 and the result will be 152

 

[the]

Wow this is amazing, and yes it works. Thanks so much!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.