Syntax for Evaluate SumProduct in VBA

[StarHunter]

Hello,
I Can't find the right syntax to type a sumproduct formula in VBA.
After searching I've come tot he fact that I can't use the SumProduct function without evaluating it but the code line is filled with """ that I can't make any sense of.
What I am trying to achieve is replace the CountIF line in this code with a Sumproduct function:

Dim MyColumn As Long, r As Long, lngLastRow As Long
    MyColumn = ActiveCell.Column
    With Sheets("Project Breakdown")
    lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
        For r = lngLastRow To 1 Step -1
            If InStr(1, Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
                 If WorksheetFunction.CountIf(.Columns(MyColumn), .Cells(r, MyColumn).Value) > 1 Then
                .Cells(r, MyColumn).Select
                ActiveCell.Rows("1:1").EntireRow.Select
                Selection.Delete Shift:=xlUp
                 End If
            End If
        Next r
    End With

Could someone please help!

[mike7952]

Can you upload an example workbook?

[Paul_Hossler]

Similar to

http://www.vbaexpress.com/forum/show...PRODUCT-in-VBA


1. Basically you have to build the WS SUMPRODUCT in a VBA string. Since you have to put quotes (") around text in the WS formula, you need to have 2 quotes in the VBA string to get one literal " in the string

VBA examples

S1 = "Now is the time"

Msgbox S1 produces Now is the time


S2 = "Now ""is"" the time"

Msgbox S2 produces Now "is" the time


2. Since .Evaluate takes a string, the appropriate ranges need to use .Address


If you manually generate a SUMPRODUCT() WS formula that works, it'd be easier to convert

[Bob Phillips]

What I am trying to achieve is replace the CountIF line in this code with a Sumproduct function:

Why, what is wrong with COUNTIF?

[StarHunter]

If you manually generate a SUMPRODUCT() WS formula that works, it'd be easier to convert

Thanks Paul.
I have already generated a manual SumProduct Formual. My data which I desire to count then remove their duplicates is always in Column "C". The formula as typed in the worksheet is =SUMPRODUCT((C:C=C4)+0). The Cell "C4" represents the active cell which keeps changing with the For Next loop.
I've tried to follow your instructions with no luck. That's what I came out with:

Dim MyColumn As Long, r As Long, lngLastRow As Long
    MyColumn = ActiveCell.Column
    With Sheets("Project Breakdown")
    lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
        For r = lngLastRow To 1 Step -1
            If InStr(1, Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
                If WorksheetFunction.Evaluate("=sumproduct((columns(MyColumn)" = "activecell.address)" + " 0 ") > 1 Then
                .Cells(r, MyColumn).Select
                ActiveCell.Rows("1:1").EntireRow.Select
                Selection.Delete Shift:=xlUp
                 End If
            End If
        Next r
    End With

The code generates an error says "Object doesn't support this property or method. The error is in the line of the evaluate formula.
What am I doing wrong?

[StarHunter]

Why, what is wrong with COUNTIF?

If you can bear with me I'll tell you my whole story.
I need to remove duplicates in Column "C" with conditions based on Column "A". Excel built in remove duplicates has no conditions and that's why I've turned to VBA.
The above code, with CountIf, works like a charm and did what I asked for but the problem appeared when the data in a cell in Column "C" has text that exceeds 255 character in length. The code generates an error then that it can't perform the CountIf.
So I turned to SumProduct to count with conditions and avoid the long text as well but I am completely lost about how to type the right text and get it to work.
Thank you

[Bob Phillips]

I would have thought that any problems you have with COUNTIF with > 255 characters you will also have with SUMPRODUCT.

Notwithstanding, I have tried to 'improve' the obvious problems in your code. I haven't tried to understand what and why you are doing it that way, or even tested it, but hopefully this will get you further than you are.#

Dim MyColumn As Long, r As Long, lngLastRow As Long

    MyColumn = ActiveCell.Column
    With Sheets("Project Breakdown")
        lngLastRow = .Cells(.Rows.Count, MyColumn).End(xlUp).Row
        For r = lngLastRow To 1 Step -1
            If InStr(1, .Cells(r, MyColumn - 2), "DIV", vbTextCompare) = 0 And InStr(1, .Cells(r, MyColumn - 2), "SEC", vbTextCompare) = 0 Then
                If .Evaluate("=Sumproduct(--('" & .Name & "'!" & Columns(MyColumn).Address & " = " & ActiveCell.Address & "))") > 1 Then
                    .Cells(r, MyColumn).Select
                    ActiveCell.Rows("1:1").EntireRow.Select
                    Selection.Delete Shift:=xlUp
                End If
            End If
        Next r
    End With