Hi,
My VBA code will copy and paste several workbooks into master list.
the cells to be pasted will be starting on cells(13,9) until cells(13,501) or Range("I13:SG13")
while the max row will be determined by Cells(Rows.Count, "F").End(xlUp).Row
Here is the progress bar code I found in internet, maxrow/column has been changed to my requirement but how to place my VBA code to this script? the one highlighted in red, I guess it
Please Login or Register to view this content.
Last edited by Faridwahidi; 05-26-2014 at 06:46 AM.
Hi,
Can anyone help me to create VBA code for percentage of completion as above to my VBA code below.
Please Login or Register to view this content.
Can you please refer this code and see whether it helps you?
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi sixthsense,
This code will make my VBA code running slowly, to many cells it runs (5000).
What I want is percentage bar will be updated after completing one row, i.e. cells(13,9) until cells(13,501)
If the cells move to the next row, cells(14,9), percentage bar will be automatically updated. max row is determined by Cells(Rows.Count, "F").End(xlUp).Row
e.g.
a = Cells(Rows.Count, "F").End(xlUp).Row
Thus range of rows is Range("F13" & a).select
Regards,
Farid
I just given that code to show how it can be written and don't take that code to your present scenario just wanted to show the method about how to achieve it.Originally Posted by Faridwahidi
Ok.. I will look into your code and add that logic in your code and post it here...
Hi Sixthsense,
Thanks for spend your time to assist me.
Try this... Code provided based on your Post #1 code
Please Login or Register to view this content.
Hi sixthsense,
There is misunderstood. I dont want percentage running by pasting value to the cell. It will return error while percentage runs until ???% and showed 3 times of message box at 100%, 200%, 300% and until 312% I have press Esc button to stop it.
You dont need to exactly follow Post #1 as I am not sure which is the accurate code.Please Login or Register to view this content.
My VBA code is as follow:
Open another files to copy and paste value start on row 13 until Cells(Rows.Count, "F").End(xlUp).Row. Just update the percentage of completion everytime when ActiveCell.row moved from 13 to 14, and 14 moved 15, and so on.
To make you clear understand on my pasting code. Here is the description
start on cells(13,9) until cells(13,501)
moved to cells(14,9) until cells(14,501) update % completion
moved to cells(15,9) until cells(15,501) update % completion
'
' '
' '
' '
'
until moved to cells(a,9) until cells(a,501) 100% completed
while a = Cells(Rows.Count, "F").End(xlUp).Row
Regards,
Farid
Last edited by Faridwahidi; 05-27-2014 at 06:12 AM.
This is my level best code...
Please Login or Register to view this content.
Hi sixthsense,
Your VBA code look better than before (nice Code), but still running out of limit, more than 100%.
I only change the sheet name and the rest remained the same.
Worksheets("Sheet1").Cells(lRow, iCol).Value = lRow
I attached the sample of small range workbooks consist of 5 persons . In fact, I have 200 persons to be updated. The estimated time is around 8-10 mins depending of Desktop processor.
The VBA code is in Consolidation SOP workbooks.
Thanks for your effort, I'm really appreciate
Can you please show the code which goes beyond 100%.
Because in your attached file I don't see any code which shows the progress
Hi Sixth sense,
There is no VBA code for the progress bar you have created in my attachment. I have tried and error to place your progress bar code in the inner loop.
The error as follow;
It runs out of limit (>100%).
The progress bar code have pasted value to the cell. overwritte my pasting code. It shoud only count the non-empty sell. NOT pasting value to the cell.
can you please open all attachment, find VBA code in workbooks ( Consolidation SOP 2014.xlsm).
Please Login or Register to view this content.
Last edited by Faridwahidi; 05-28-2014 at 01:55 AM.
Hi sixthsense,
I have tried on this, but it runs out of limit & paste value to the cells. it should only count the completed cells.
Sorry I don't have that much of time to check your code and apply the progression.
Please apply the given logic since it is going to be a simple arithmetical calculation like dividing the count based on the number of loops.
Hi sixthsense,
when I press Esc button to cancel, Debug error stop at lCounter = lCounter + 1
Because at the time of pressing Esc the code was interrupted at that line because of pressing ESC.
Hi sixthsense,
The Esc button press when it runs out of limit (>100%).
and your progress bar code should not paste value to the cells. It should only count the cells.
Hi sixthsense,
I have created a simple VBA code. How to place progress bar.
The progress bar code should not paste value to the cells, but only count the completed cells.
Please Login or Register to view this content.
Last edited by Faridwahidi; 05-28-2014 at 05:35 AM.
I'm not 100% sure but looking at your code here:
I think the problem could be that you are looping through the "progress bar" bit several times as part of the For lngVar = LBound(var) To UBound(var) but I don't think you reset the lCounter that you base your progress bar on, so for the 2nd, 3rd, 4th times etc, it will be going from whatever the previous lCounter value was. So maybe try adding, Lcounter = 0 before the for lrow = startrow to maxrow bit, e.g.:Please Login or Register to view this content.
Please Login or Register to view this content.
Hi raguldy,
Thanks for willing to assist me. With Lcounter = 0, after reach 100%, the percentage back to 0% and it runs again.
Another problem is the progress bar code have pasted value to the cells while running to calculate percentage. So, my pasting code have been overwritten, When i check the output, it show number 13 ---501.
This is the code.
I try to change it to count, but it does not work.Please Login or Register to view this content.
I think, it is enough to update the percentage completion after macro have completed pasting value to one row. e.g. cells(13,9) to cells(13,501). before activeCell moved to the next cells(14,9) until cells(14,501) and so on until maxrow= Cells(Rows.Count, "F").End(xlUp).RowPlease Login or Register to view this content.
Please help me, thanks a lot.
Last edited by Faridwahidi; 05-28-2014 at 08:58 AM.
Ok, I see, try:
Please Login or Register to view this content.
Hi ragulduy,
Unbelievable, it works .
But the percentage jump from 18% to 49% and 55% to 89% . I guess, it could be due to one of the files completed pasting value.
I think that the percentage should go up in equal steps, but it will appear to skip some because if it doesn't find the worksheet it will move to the next counter and so will update quicker than you will be able to see. So the progress bar is based on the counter, not the number of sheets to open.
Hi ragulduy,
Thanks for the information.
However, i think excel can only give estimation % but cannot give accurate %.
To make it easy to check, I have created simple code and run a single step using F8 button.
Excel display:Please Login or Register to view this content.
1% at row 25, should be at row 50
2% at row 76, should be at row 100
3% at row 128, should be at row 150
That's because you are rounding it to the nearest percent.
so row 128 = 128/5000 = 2.56% which is rounded to 3%.
Either change format(percentcomplete,"0%") to format(percentcomplete,"0.0%") or if you only want it to display 3% once 3% has been reached rather than rounding up then:
percentcomplete = worksheetfunction.rounddown(i/5000,2)
Hi ragulduy,
Thanks a lot. I have gained my knowledge.
Just to get confirmation, Is it possible to display time left?
If yes, I will ask in a new thread.
I suppose you could come up with a very rough estimate but it would be pretty much redundant.
I would suggest time a bunch of them and see how long they take, calculate the average of these times, divide this average by 100 to get the time for each percent and then use this to calculate how long is left (i.e. time taken per percent * number of percent remaining)
Hi ragulduy,
Here is the link for a new thread. To make it easy to understand, I only use simple code same as post #24.
I just want to get a basic learning, and clearly understand before go to complicated script.
http://www.excelforum.com/excel-prog...ml#post3712532
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks