Macro to "Find - Replace" in Background

raj08536

Active Member
Joined
Aug 16, 2007
Messages
301
Office Version
  1. 365
Platform
  1. Windows
Hi Experts,

Here is an existing Macro to find and replace links in 100 worksheets.

Sub Replace()

Fnd = Sheets("000.Current month").Cells(10, 2).Value
Rplace = Sheets("000.Current month").Cells(11, 2).Value

For Each sht In Worksheets
If sht.Name <> "000.Current month" Then
sht.Activate
Cells.Replace What:=Fnd, Replacement:=Rplace, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End If
Next sht

End Sub

Understandably It takes very long and therefore, hold me to use excel. Can I change macro to work in the background while I am doing other assignments in Excel?

Thanks

Raj
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
584
Office Version
  1. 365
Platform
  1. Windows
You could also just start another instance of Excel - have one running a macro over the workbook, and then work on the second.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,793
Office Version
  1. 2013
Platform
  1. Windows
In addition to Dan_W's suggestion, within time consuming procedures it is recommended (in connection with the architecture and behaviour of a multi-tasking OS like the Windows OS) to let the OS know at regular intervals that your VBA is still running by yielding to the OS using the DoEvents statement. One wants to prevent Windows (because of the suspicion of an infinite loop) from decreasing the priority of the running process (i.e. your macro) when assigning processor time.
Rich (BB code):
Sub Replace()

    Fnd = Sheets("000.Current month").Cells(10, 2).Value
    Rplace = Sheets("000.Current month").Cells(11, 2).Value
   
    For Each sht In Worksheets
        If sht.Name <> "000.Current month" Then
            sht.Activate
            Cells.Replace What:=Fnd, Replacement:=Rplace, LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        End If
        Dim i As Long
        i = i + 1
        If i Mod 2 = 0 Then DoEvents
    Next sht
End Sub
 

raj08536

Active Member
Joined
Aug 16, 2007
Messages
301
Office Version
  1. 365
Platform
  1. Windows
Ok. I got it.

I want to create a file and store this macro and call this file as "FindReplace.xlsm" path "C;\\testing"

I have multiple files in the same path or can be in other directory. How can I call Replace Macro from "FindReplace.xlsm" in each file. I think in each file I have to create another macro to open "FindReplace.xlsm" and call replace macro

How do I write the VBA code in each file?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,793
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

If you're in the need of processing multiple files, it seems to be more efficient to make just one macro procedure (in one workbook) responsible for that, rather then duplicating your code accross all the workbooks to be processed. Do you agree?
 

raj08536

Active Member
Joined
Aug 16, 2007
Messages
301
Office Version
  1. 365
Platform
  1. Windows
If you're in the need of processing multiple files, it seems to be more efficient to make just one macro procedure (in one workbook) responsible for that, rather then duplicating your code accross all the workbooks to be processed. Do you agree?
so how do you suggest?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,793
Office Version
  1. 2013
Platform
  1. Windows
You wrote that you intended to call the FindReplace macro from multiple workbooks. This implies that each workbook from which you wanted to call that macro consists of any number of worksheets, but that no worksheet has the name "000.Current month". Am I right?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,793
Office Version
  1. 2013
Platform
  1. Windows
The code below is not as efficient as it could be (and prone to errors), but it illustrates what I mean. The ProcessWorkbooks procedure is the main procedure and calls the RReplace procedure several times, each time for a different workbook.
VBA Code:
Sub ProcessWorkbooks()

    Dim oWb As Workbook
    Dim Fnd As Variant
    Dim Rplace As Variant

    With ThisWorkbook.Sheets("000.Current month")
        Fnd = .Cells(10, 2).Value
        Rplace = .Cells(11, 2).Value
    End With

    Set oWb = Workbooks.Open("C:\folderA\xlfile01.xlsx")
    Replace oWb, Fnd, Rplace
    oWb.Close
    
    Set oWb = Workbooks.Open("C:\folderB\xlfile02.xlsx")
    Replace oWb, Fnd, Rplace
    oWb.Close

    Set oWb = Workbooks.Open("C:\folderC\xlfile03.xlsx")
    Replace oWb, Fnd, Rplace
    oWb.Close

End Sub


Sub RReplace(ByVal argWb As Workbook, ByVal argFind As Variant, ByVal argReplace As Variant)
    Dim sht As Worksheet
    Dim i as Long

    For Each sht In argWb.Worksheets
        Cells.Replace What:=argFind, Replacement:=argReplace, LookAt:=xlPart, _
                      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                      ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        i = i + 1
        If i Mod 2 = 0 Then DoEvents
    Next sht
End Sub
 

Forum statistics

Threads
1,141,001
Messages
5,703,656
Members
421,309
Latest member
ray crad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top