PDA

View Full Version : Error with sumproduct



YasserKhalil
10-30-2010, 01:07 PM
Hi everybody
I wrote the following code but I encountered an error at the line of sumproduct

Here's the code
Dim LastRow As Integer: Dim cel, rng, rngkind, rngsales As Range
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Range("D2:D" & LastRow)
Set rngkind = Range("Kind")
Set rngsales = Range("Sales")
Application.ScreenUpdating = False
For Each cel In rng
cel.Offset(0, 2) = Application.WorksheetFunction.SumProduct((rngkind = cel.Value) * (rngsales))
Next cel
Application.ScreenUpdating = True

YasserKhalil
10-30-2010, 03:45 PM
UP

mdmackillop
10-30-2010, 04:45 PM
From memory, SumProduct is not available as a WorksheetFunction within vba. You can use Evaluate though.

Bob Phillips
10-30-2010, 04:57 PM
cel.Offset(0, 2) = Application.Sumif(rngkind, cel.Value, rngsales)

YasserKhalil
10-30-2010, 05:17 PM
Thank you very much
It's nice to have different solutions
Here's another link to the same problem
http://www.mrexcel.com/forum/showthread.php?t=505572

Bob Phillips
10-30-2010, 05:30 PM
Oh, so you are a cross-poster. Thanks for the info, I know to ignore your posts now.

YasserKhalil
10-30-2010, 05:50 PM
Oh, so you are a cross-poster. Thanks for the info, I know to ignore your posts now.
Sorry Mr. xld but I was in a bad need to correct the code
And when I can't get a quick solution, I tried MrExcel to have a quick one
My apology for you...!!

Bob Phillips
10-31-2010, 04:25 AM
You don't have to apologise to me, I am a free person and I make my own choices just as you do.

I don't like cross-posting as I feel it shows a total disregard for the people who try and help provide solutions, and as I such as soon as I know about cross-posting I will not get involved in trying to help provide that person a solution, even if it is clear that they still require one.

And, I take that approach forward into future posts.