#1
|
|||
|
|||
progress of the calculation
Hi Is it possible to add to an existing VBA macro a line of code which will show some feedback on the progress of the calculation. Like Progress Bar, so that it will be possible to estimate whether the calculation is at the beginning or towards the end? I have a macro that needs to work a full day and maybe more. Thanks. Yacov |
#2
|
|||
|
|||
Depends on what the macro is actually doing i guess, but a Macro running for a full day would be a concern anyway
|
#3
|
|||
|
|||
the macro is a bulk find and replace
|
#4
|
|||
|
|||
So you could add a counter once it has completed a find and replace? Can you work out how many find and replaces its needs to do or how many cells/worksheets it is looking at?
How many is it finding and replacing? What is the scope of each find and replace? An example workbook would help |
#5
|
|||
|
|||
Column A has the old text 23203 cells. Column B has the new text with the same amount of cells (23203). In column C I will put the source files you want to modify (about 5000 cells).
I attached the file without the text because the text is not in English. |
#6
|
|||
|
|||
Is the old text one word and you are replacing 1 letter? eg London <n> Lodo
or is it a statement that you then want to replace a specific word? The Brown Car <Brown> The Car Are you changing or deleting? |
#7
|
|||
|
|||
All the cells contain sentences from the Torah, when I want to add a serial number to them.
For example: "In the beginning God created the heavens and the earth" (column 1). I am interested in changing in the "beginning God created the heavens and the earth 1" (Column B). The number (in this case 1) is the only addition. And so on to sentence 2 we come out in line 2 I add the digit 2 and so on until the end it is line 23203 |
#8
|
|||
|
|||
I cant get it to do what you are are saying it does - can you fill in a couple of rows in english?
or are you are apending a serial number to each row? |
#9
|
|||
|
|||
Sorry for the English, I have attached a new file with an example
|
#10
|
|||
|
|||
To be honest i cant work out what you are trying to do, but if you want some kind of progress bar you can add a row count that shows on another sheet (this only show every 100 rows, but you can change that) - you will have to update your code so that the find and replace are working on the orginal sheet and not the 'Progress' sheet
Code:
Sub BulkReplace() Dim Rng As Range, SourceRng As Range, ReplaceRng As Range Dim rCount As Long '<New Dim tCount As Long '<New Dim aSheet As String '<New aSheet = ActiveSheet.Name On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set SourceRng = Application.InputBox("Source data:", "Bulk Replace", Application.Selection.Address, Type:=8) Err.Clear If Not SourceRng Is Nothing Then Set ReplaceRng = Application.InputBox("Replace range:", "Bulk Replace", Type:=8) Err.Clear If Not ReplaceRng Is Nothing Then Application.ScreenUpdating = False Worksheets.Add.Name = "Progress" tCount = Worksheets(aSheet).Cells(Rows.Count, 1).End(xlUp).Row '<New Worksheets(aSheet).Activate For Each Rng In ReplaceRng.Columns(1).Cell SourceRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value rCount = rCount + 1 If Int(rCount) / rCount = 1 Then '<New Application.ScreenUpdating = False '<New Worksheets("Progress").Range("a1") = rCount & "/" & tCount '<New Worksheets("Progress").Activate '<New Application.ScreenUpdating = True '<New End If '<New Next End If End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub |
#11
|
|||
|
|||
thanks a lot.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Progress bar in a Table | Mona0306 | PowerPoint | 0 | 04-04-2021 11:54 AM |
Calculate the progress per day | pm1110 | Excel | 2 | 09-16-2015 02:34 AM |
how to add progress bar to the following vba | sivasucmc | Word VBA | 2 | 07-27-2014 04:44 PM |
updating task progress | ketanco | Project | 1 | 08-16-2012 05:09 AM |
How to create a progress bar | AfterLife6 | Excel | 1 | 07-31-2012 08:43 PM |