If(Sumproduct) VBA

[The_Rock]

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:

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

Appreciate your help with this.
Thanks

 

[pgc01]

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()

 

[The_Rock]

Hi PGC
Thanks for your advice.

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.

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

 

[pgc01]

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

'...

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.

 

[pgc01]

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

replacing your original:

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

with something like

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

 

[The_Rock]

Thanks for your help PGC.
rSubId and rAct_Rqd are named ranges.

I'll give the above a go. Cheers