Thanks Daniel for a great help but
i appriciate if you can able to give me the complete macro code for my file
so i have no need to modify any code
hope you understood it is easy for you to update the complete code rather
then me
thanks once again.
On Wed, Feb 23, 2011 at 10:29 PM, Daniel <dcolarde...@free.fr> wrote:
> 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
>
--
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
