PDA

View Full Version : Syntax for Evaluate SumProduct in VBA



StarHunter
01-08-2017, 06:37 AM
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
01-08-2017, 09:03 AM
Can you upload an example workbook?

Paul_Hossler
01-08-2017, 09:03 AM
Similar to

http://www.vbaexpress.com/forum/showthread.php?58136-SUMPRODUCT-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
01-08-2017, 11:58 AM
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
01-09-2017, 12:57 AM
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
01-09-2017, 01:05 AM
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
01-09-2017, 01:51 AM
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