Always best to use the actual file.
Don Guillett
SalesAid Software
dguille...@gmail.com
From: maulik desai
Sent: Monday, November 21, 2011 10:52 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Required Progres bar / Status bar while macro
runinng....
Hi Don,
Thanks you so much for your response but i just want to tell that actual batch
code are not in series like batch 1, batch 2 etc it is based on process & sub
process name also based on location & then No so i can say there is no fixed
format for the batch code & also give me 1 days time i will update the
remaining code & send u batch mean while your can change the code becasue
currenly it is based on batch 1 & batch 2 as u mentioned & also can i get the
progress bar for the same . I must say it is very efficient code thanks for
that for now it took 1 or 2 sec to update
& i hope in real file it will help me also ....
Thanks
On Sun, Nov 20, 2011 at 11:17 PM, dguillett1 <dguille...@gmail.com> wrote:
I have gone thru and re-written your code to be more efficient. I see no real
need to do with a worksheet change event as it will be fast even with 750 rows.
It starts with a clean sheet beginning at row 7 assuming your Batch’s are
actually named Batch 1, batch 2, etc. Click to see!!
Don Guillett
SalesAid Software
dguille...@gmail.com
From: maulik desai
Sent: Sunday, November 20, 2011 3:21 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Required Progres bar / Status bar while macro
runinng....
Hi Don,
Thanks for your reply & i am so sorry for late reply acctually i can not able
to use gmail from my office pc & here i am attaching the Format of my file as
requested, kindly check the macro code i want to requce the code size &
normally it takes 20 to 30 mins currently it has just a dummy data so it will
give faster result but acctually on "Batch" sheet it contains more then 750
rows on a monthly basis....
Request you to kindly do the needful
thanks in advance.
On Mon, Nov 14, 2011 at 6:47 PM, dguillett1 <dguille...@gmail.com> wrote:
Attach your file with a reply to this msg.
Don Guillett
SalesAid Software
dguille...@gmail.com
From: maulik desai
Sent: Sunday, November 13, 2011 10:31 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Required Progres bar / Status bar while macro
runinng....
Hi Team,
I having 1 excel sheet in that i have apply below Sumproduct macro code 72
times to get output on my database (This code is provided by Daniel of this
group ) my But my problem is it take about 20 to 30 mins to run the code total
550 row(not fixed) x 72 columns ) what I want that can i get a progress bar
which shows me status like " 3 outof 72 columns completed" or 10%,15% ,30%
completed like that....
also if can help to reduce the code size to work faster
Please find below code provided by Mr.Daniel of this group.
Thanks in advance
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
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
Thanks & Regards,
Maulik Desai
9967363926
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
Thanks & Regards,
Maulik Desai
9967363926
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
