SUMPRODUCT in VBA... conditional sum

  • Thread starter Thread starter Mark Drayton
  • Start date Start date
M

Mark Drayton

Hi

I am trying to recreate a SUMPRODUCT equation into VBA to increas
speed, performance etc of a spreadsheet with a large amount of data.
My current equation is:

=SUMPRODUCT(('CURRENT MTH 03'!$A$5:$A$20244="Purchase Mino
Equipment")*('CURRENT MTH 03'!$J$5:$J$20244=$D250)*('CURRENT MT
03'!$E$5:$E$20244))

To make things more interesting I'm trying to write a macro that JUS
populates the value and not the equation.

I found this code on www.exceltip.com & am trying to enter the code i
the Bold text:


Sub Loop8()

Do

ActiveCell.Value = WorksheetFunction.*Average(ActiveCell.Offset(0
-1).Value, ActiveCell.Offset(0, -2).Value)*

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub



I'd really appreciate your help to get me out of this one.

Thanks

Mar
 
Hi Mark
not possible to use application.worksheetfunction in this context.
Works for SUMPRODUCT only in the standard usage like
=SUMPRODUCT(matrix1,matrix2,...)

Conditions are not possible. In addition I doubt VBA would be faster
than using formulas as you have added the VBA overhead. So I'd suggest
inserting the formulas and pasting them again with only their values
 
First, many of the regulars read news in plain text, so bold won't show
up.

Second, *nothing* you try using VBA will be faster than XL's native
functions. If you're having trouble with VLOOKUPs now, your workbook
will slow to a crawl if you implement the function in VBA.

You may want to take a look at Charles Williams' site for tips on
improving performance:

http://www.decisionmodels.com/optspeed.htm
 
Thanks for getting back quickly & the tip on using Bold.

Is there any other VBA code for conditional summing?

Gotta get on trying;)


Thanks

Mar
 
Hi Mark
in VBA you would loop through each row and check the conditions. This
would definetly be much slower than any worksheet formula function. So
to be honest worksheet formulas are in nearly all cases the fastest way
to go.

you may consider using a pivot table if you want some specific data
analysis 8not aure from your formula)
 
Back
Top