Here is an example to replace sheet Batch column K with a macro:
Sub test()
'sheet Batch column K
With Sheets("Batch")
For Each c In .Range(.[A7], .[A65536].End(xlUp))
.Cells(c.Row, 11) = Evaluate("sumproduct((DB!$A$2:$A$46803=Batch!$A" & c.Row
& ")*(DB!$AP$2:$AP$46803=""Yes""))")
Next c
End With
End Sub
You should run the macro each time you change, add or delete a value in
column A; place the following macro in the sheet module :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 6 Then
Cells(Target.Row, 11) = Evaluate("sumproduct((DB!$A$2:$A$46803=Batch!$A"
_
& Target.Row & ")*(DB!$AP$2:$AP$46803=""Yes""))")
End If
End Sub
You’ll have to do the same for others formulas.
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de maulik desai
Envoyé : mardi 22 février 2011 18:55
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ How to use sumproduct Formula in Macro
I having Database with 4Sheets and i have used Sumproduct Formula in many
Columns in my database. i have apply the formulas on every columns but the
prolem is the file becomes very heavy & it take to much time to give the
output
is there any way to use the samproduct function with the help of macro
sample file attached i am having large database
Request you to kindly provide the solution for the same
--
Thanks & Regards,
Maulik Desai
9967363926
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
