SumProduct not Working in a Macro

B

Booey

Can someone please help - this is driving me nuts. I am wanting to use
SumProduct in a Macro but it does not compile.

The reason is that I am writing a macro to delete duplicate rows based on
all columns of data, in some cases up to 50 rows.

A simple example to highlight what I am trying to do - Lets say columns A to
G have data in them in and the data is in rows 2 - 10, headings are in row 1.
Firstly I concatenate all the data across Columns A to G and place the
result in Column H. I do this for all 10 rows (including the heading).

The next thing I do is test column H in row 10 to see how many occurrences
there are in the range H2:H10. If there is more than one I delete the entire
row. I then work my way up to row 2.

If I put the formula "=SUMPRODUCT(--(H2:H10=H10))" into cell I10 in the
spreadsheet itself I get a result no problem, let's say 2.

Whenever I try to use SumProduct in a Macro it won't give a result. To test
it I typed the following in to get a result:

Range("I10") = Application.WorksheetFunction.SumProduct(--(H2:H10 = H10))

When I put this into a Macro the line goes red and the : between the H2:H10
is highlighted and it displays:
Compile Error:
Expected: )

I have tried creating a string variable that contains the "H2:H10 = H10" but
when I put it in it doesn't work. I have tried many other ways but nothing
seems to give me a result that I can work with.
 
J

Jacob Skaria

Why do you need to use SUMPRODUCT(). Try
Range("I1:I10").Formula = "=COUNTIF($H$1:$H$10,H1)"

OR try the below macro instead which use the Advanced Filter option

Sub Macro2()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:G" & lngLastRow).AdvancedFilter Action:=xlFilterInPlace,
Unique:=True
For lngRow = 1 To lngLastRow
If Rows(lngRow).Hidden Then Rows(lngRow).Delete
Next
ActiveSheet.ShowAllData
End Sub
 
J

Joe User

Booey said:
Range("I10") =
Application.WorksheetFunction.SumProduct(--(H2:H10 = H10))

There are ways to make the WorksheetFunction.SumProduct work.

But try:

Range("I10") = Evaluate("SumProduct(--(H2:H10 = H10))")

or for that functionality, more simply:

Range("I10") = Evaluate("countif(H2:H10,H10)")


----- original message -----
 
B

Booey

Hi Jacob. I need to use SUMPRODUCT as COUNTIF only works on cell values up
to 255 characters, whereas this is not a problem with SUMPRODUCT. Some of
the concatenated cells might be as much as 1000 characters.

I eventually solved it using the following code:

mFormula = "SUMPRODUCT(--(" & ConcCol & 2 & ":" & ConcCol & R &
"=" & ConcCol & R & "))"
Q = Application.Evaluate(mFormula)

Where mFormula is defined as String and Q as Long. ConcCol and R are
variables in a For Next loop that I use as I work up from the bottom to the
top of the data.

Thanks for trying to help.
 
B

Booey

Hi Joe.

I eventually solved it using the following code:

mFormula = "SUMPRODUCT(--(" & ConcCol & 2 & ":" & ConcCol & R &
"=" & ConcCol & R & "))"
Q = Application.Evaluate(mFormula)

Where mFormula is defined as String and Q as Long. ConcCol and R are
variables in a For Next loop that I use as I work up from the bottom to the
top of the data.

I had to muck around a bit to get the syntax exactly right in mFormula, but
it works perfectly.

Thanks for trying to help.
 
J

Jacob Skaria

Did you try the macro..
--
Jacob


Booey said:
Hi Jacob. I need to use SUMPRODUCT as COUNTIF only works on cell values up
to 255 characters, whereas this is not a problem with SUMPRODUCT. Some of
the concatenated cells might be as much as 1000 characters.

I eventually solved it using the following code:

mFormula = "SUMPRODUCT(--(" & ConcCol & 2 & ":" & ConcCol & R &
"=" & ConcCol & R & "))"
Q = Application.Evaluate(mFormula)

Where mFormula is defined as String and Q as Long. ConcCol and R are
variables in a For Next loop that I use as I work up from the bottom to the
top of the data.

Thanks for trying to help.
 
B

Bob Phillips

Joe User said:
There are ways to make the WorksheetFunction.SumProduct work.

But try:

Range("I10") = Evaluate("SumProduct(--(H2:H10 = H10))")

or for that functionality, more simply:

Range("I10") = Evaluate("countif(H2:H10,H10)")

If you are going to use COUNTIF there is no need for evaluate, that is only
necessary for array formulae.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top