# If(Sumproduct) VBA

#### The_Rock

##### Board Regular
Hi folks
I am trying to write the following formula as VBA:

In Col CF:
=IF(AM3="Rejected","-",IF(SUMPRODUCT((rSubId=AB3)*(rAct_Rqd="Duplicate"))>0,"CURRENT DUPLICATE","-"))

I have written this as:
Code:
``````Sub Current_Dup()

Dim wsAZV As Worksheet, rng As Range, lr As Long
Dim i, j As Integer

Set wsAZV = Sheets("Azure Validation (2)")
i = Application.WorksheetFunction.CountA(Sheets("Azure Validation (2)").Range("B:B"))

''Test in (Cell CF)
For j = 3 To i + 1
'1. Ignore Rejected
If Cells(j, 39) = "Rejected" Then
Cells(j, 84).Value = "-"
'2. Calculate if duplicate to current
ElseIf Application.WorksheetFunction.SumProduct((rSubId = Cells(j, 28)) * (rAct_Rqd = "Duplicate")) > 0 Then
Cells(j, 84).Value = "CURRENT DUPLICATE"
Else
Cells(j, 84) = "-"
End If

Next j

MsgBox "Done"

End Sub``````

In the Sumproduct line I get the following error message:
run-time error '1004'
unable to get the SumProduct property of the WorkSheetFunction class

Thanks

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi

The parameters of WorksheetFunction.Sumproduct() must be arrays, which is not the case in your code, so you get an error.

If you wanted to use the function you'd have to build the arrays corresponding to the comparisons.

I think that in this case, however, it's much easier to use CountIfs() directly.

Remark: another solution would be to use Evaluate()

Hi PGC

I haven't used Evaluate() before so have tried to implement it below.
Now, the issue is that it checks for the first 'IF' statement (rejected) and then defaults everything to 'Current Duplicate'. It doesn't check the 3rd criteria which is if its neither of the first two then "-".
I don't know if the evaluation is in the right place.

Code:
``````Sub try_evaluate()

Dim wsAZV As Worksheet: Set wsAZV = Sheets("Azure Validation (2)")
Dim i, j As Integer

i = Application.WorksheetFunction.CountA(Sheets("Azure Validation (2)").Range("B:B"))

For j = 3 To i + 1
'1. Ignore Rejected
If Cells(j, 39) = "Rejected" Then
Cells(j, 84).Value = "Rej"
'2. Calculate if duplicate to current
sFormula = "SumProduct((rSubId = Cells(j, 28)) * (rAct_Rqd = ""Duplicate""))"
Evaluate (sFormula)
ElseIf sFormula >= 0 Then
Cells(j, 84) = "CURRENT DUPLICATE"
Else
'3. If not 1 or 2 then "-"
Cells(j, 84) = "-"
End If

Next j

End Sub``````

Hi

You have to capture the result of the evaluation.

Also,
. rSubId and rAct_Rqd seem to be vba range variables (or are they named ranges?)
. Cells(j, 28) is a vba cell

You cannot include them in a double quoted string or they will be considered as text.

If I understood correctly, you need something like (not tested):

Code:
``````'...

Dim sFormula As String ' the formula to evaluate
Dim vFormula As Variant ' the result of the evaluation of the formula

Dim rSubId As Range, rAct_Rqd As Range
Dim j As Long, i As Long

'...

For j = 3 To i + 1

'1. Ignore Rejected
If Cells(j, 39) = "Rejected" Then
Cells(j, 84).Value = "-"

'2. Calculate if duplicate to current
Else
sFormula = "SumProduct((" & rSubId.Address & "=" & Cells(j, 28).Value & ")*(" & rAct_Rqd.Address & "=""Duplicate""))"
vFormula = Evaluate(sFormula)

If vFormula > 0 Then
Cells(j, 84).Value = "CURRENT DUPLICATE"
Else
Cells(j, 84) = "-"
End If
End If

Next j

' ...``````

Remark: As I said this is not tested, just to give you an idea of the syntax
Also it may be a good idea to test the result of the SumProduct() to see if it's not an error value.

... but, as I said before, it seems it would be easier with CountIfs()

Code:
``````        ElseIf Application.WorksheetFunction.SumProduct((rSubId = Cells(j, 28)) * (rAct_Rqd = "Duplicate")) > 0 Then
Cells(j, 84).Value = "CURRENT DUPLICATE"``````

with something like

Code:
``````        ElseIf Application.WorksheetFunction.CountIfs(rSubId, Cells(j, 28).Value, rAct_Rqd, "Duplicate") > 0 Then
Cells(j, 84).Value = "CURRENT DUPLICATE"``````

rSubId and rAct_Rqd are named ranges.

I'll give the above a go. Cheers

Replies
5
Views
172
Replies
14
Views
406
Replies
6
Views
130
Replies
0
Views
122
Replies
2
Views
224

### Forum statistics

1,196,478
Messages
6,015,442
Members
441,895
Latest member
Zululander ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back