The_Rock
Board Regular
- Joined
- Jul 2, 2007
- Messages
- 174
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:
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
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
Appreciate your help with this.
Thanks