Thakns All
but i want this progress bar with my exsiting macro code & not in seprate
code
as far i understood for progress bar u have to do loop code
but i dont understand how to apply processbar code in my exsisting
Sumproduct macro code
kindly provide your help & provide me the solution
Thanks
On Wed, Dec 7, 2011 at 7:26 PM, Sourabh Salgotra <rhtdmja...@gmail.com>wrote:
> chk attachment
>
>
> On Tue, Dec 6, 2011 at 12:51 PM, maulik desai <mauliksde...@gmail.com>wrote:
>
>> hi don,
>> thanks for the reply i am working for international firm as sr. Mis
>> executive and this is for 1 of my monthly report prepration part i am
>> from mumbai and we have every week new joinees coming to company and
>> according to that we have to create diff batch codes bases on
>> location,process,subprocess and batch code below is the same exp for
>> ur ref which is updated in a column
>>
>> batch code updated as
>> chsdc 21
>> chbserrca 12
>> ch-slh-batch 20
>> ch-acd-batch 3
>> gn/avf/dc doc/22
>> ok/eb sales/batch 32
>> gn/nahd nh/batch 12_11
>> cwcdrlcc 1
>> tb/co/batch 163
>> bc/mnb/batch 3
>> vf/ert/eb/batch ss 5
>>
>> above mentioned batch codes are just part of total batches so aa i
>> told u befor there is no fixed style of batch code so request u to
>> kindly apply code with free text logic like if from a7 if anything is
>> entered it should give the output and same logic implimented in
>> original file
>> hope i have understood u properly...
>>
>> Request u to kindly make the necessury changes and resend the updated file
>>
>> thanks in advance
>>
>> On 12/4/11, dguillett1 <dguille...@gmail.com> wrote:
>> > 1. You give NO examplePerhaps a list and then “for each item in
>> > range(“a2:a11”)
>> > 2. Adaptable to a worksheet_change event
>> > 3. Can be done
>> >
>> > Who are you_________,Where are you___________Who do you work
>> > for____________What is this for??_________________
>> >
>> > Don Guillett
>> > SalesAid Software
>> > dguille...@gmail.com
>> >
>> > From: maulik desai
>> > Sent: Sunday, December 04, 2011 1:41 AM
>> > To: excel-macros@googlegroups.com
>> > Subject: Re: $$Excel-Macros$$ Required Progres bar / Status bar while
>> macro
>> > runinng....
>> >
>> > Thanks don & very sorry for late reply i was not in town for a week
>> >
>> > I just want to some changes from you
>> >
>> > I have added the macro lines as requested by you (PFA file)kindly check
>> the
>> > same & modify according to requirement (also mention some comments in
>> macro
>> > code kindly check also)
>> >
>> > 3 changes i want from you that
>> >
>> > 1.batch code is not based on Batch 1 & batch 2 & on as i mentioned in my
>> > previous mail it could be any thing so kindly make the changes in bach
>> code
>> > area
>> >
>> > 2.In my old file i have used worksheet change event as once i run the
>> code &
>> > after that i have make many changes as per the requirement & that is
>> not for
>> > all batches so it is very diff to always run the complete macro for
>> small
>> > small changes that is why i have apply worksheet event so when ever i
>> change
>> > the data for some batch i used to press F2 in that batch code & details
>> gets
>> > changed so that thing is very imp for this report
>> >
>> > 3.as every month i have to apply macro for more then 550 unique
>> batches its
>> > take 15 to 20 mins to update so i want the process bar which can show
>> me the
>> > status like in % or how many columns are remaining.....
>> >
>> > Hope i understood u properly
>> >
>> > Thanks in advance & sorry once again for late reply....awaiting for your
>> > reply with updated code..
>> >
>> >
>> > On Mon, Nov 21, 2011 at 10:41 PM, dguillett1 <dguille...@gmail.com>
>> wrote:
>> >
>> > 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
>> >
>> >
>> >
>> >
>> > --
>> > 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
>>
>
>
>
> --
> mujhay dukh is baat ka nahin kay meri zaat ko
> muntashir karny walay haath tairy thy
> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
> samaitnay walay haath tairy na thy
>
>
>
> --------------------------------------------------------------------------------
>
>
> Thanks & Regards
> Sourabh
> Contact Numbers: +91-94630-49202
> Website:http://adhurapyaar.co.cc
>
>
>
> --
> 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
