Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-29-2021, 10:10 PM
yacov yacov is offline progress of the calculation Windows 10 progress of the calculation Office 2016
Competent Performer
progress of the calculation
 
Join Date: Oct 2019
Posts: 139
yacov is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 08-29-2021, 10:14 PM
Purfleet Purfleet is offline progress of the calculation Windows 10 progress of the calculation Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Depends on what the macro is actually doing i guess, but a Macro running for a full day would be a concern anyway
Reply With Quote
  #3  
Old 08-29-2021, 10:20 PM
yacov yacov is offline progress of the calculation Windows 10 progress of the calculation Office 2016
Competent Performer
progress of the calculation
 
Join Date: Oct 2019
Posts: 139
yacov is on a distinguished road
Default

the macro is a bulk find and replace
Reply With Quote
  #4  
Old 08-29-2021, 10:23 PM
Purfleet Purfleet is offline progress of the calculation Windows 10 progress of the calculation Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 08-29-2021, 10:37 PM
yacov yacov is offline progress of the calculation Windows 10 progress of the calculation Office 2016
Competent Performer
progress of the calculation
 
Join Date: Oct 2019
Posts: 139
yacov is on a distinguished road
Default

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.
Attached Files
File Type: xlsm BULK FIND REPLACE.xlsm (162.7 KB, 6 views)
Reply With Quote
  #6  
Old 08-29-2021, 10:52 PM
Purfleet Purfleet is offline progress of the calculation Windows 10 progress of the calculation Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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?
Reply With Quote
  #7  
Old 08-29-2021, 11:09 PM
yacov yacov is offline progress of the calculation Windows 10 progress of the calculation Office 2016
Competent Performer
progress of the calculation
 
Join Date: Oct 2019
Posts: 139
yacov is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 08-29-2021, 11:22 PM
Purfleet Purfleet is offline progress of the calculation Windows 10 progress of the calculation Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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?
Reply With Quote
  #9  
Old 08-29-2021, 11:47 PM
yacov yacov is offline progress of the calculation Windows 10 progress of the calculation Office 2016
Competent Performer
progress of the calculation
 
Join Date: Oct 2019
Posts: 139
yacov is on a distinguished road
Default

Sorry for the English, I have attached a new file with an example
Attached Files
File Type: xlsm BULK FIND REPLACE2.xlsm (162.7 KB, 9 views)
Reply With Quote
  #10  
Old 08-30-2021, 11:39 AM
Purfleet Purfleet is offline progress of the calculation Windows 10 progress of the calculation Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #11  
Old 08-30-2021, 08:16 PM
yacov yacov is offline progress of the calculation Windows 10 progress of the calculation Office 2016
Competent Performer
progress of the calculation
 
Join Date: Oct 2019
Posts: 139
yacov is on a distinguished road
Default

thanks a lot.
Reply With Quote
Reply

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
progress of the calculation Calculate the progress per day pm1110 Excel 2 09-16-2015 02:34 AM
progress of the calculation how to add progress bar to the following vba sivasucmc Word VBA 2 07-27-2014 04:44 PM
progress of the calculation 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft