progress of the calculation

[yacov]

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

[Purfleet]

Depends on what the macro is actually doing i guess, but a Macro running for a full day would be a concern anyway

[yacov]

the macro is a bulk find and replace

[Purfleet]

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

[yacov]

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.

[Purfleet]

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?

[yacov]

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

[Purfleet]

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?

[yacov]

Sorry for the English, I have attached a new file with an example

[Purfleet]

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

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

[yacov]

thanks a lot.