Add Inputs for Sheet and Cell Range for Multiple Excel File Find & Replace

RobertN

New Member
Joined
Jan 10, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have been using the code below to find & replace text in multiple excel workbooks, but would like to add inputs to specify sheet name and cell range. I am super novice and have tried playing with the code but have had no luck.

Sub ReplaceInFolder()
Dim strPath As String
Dim strFile As String
Dim wbk As Workbook
Dim wsh As Worksheet
Dim strFind As String
Dim strReplace As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.StatusBar = False

strFind = InputBox("Enter text to find")
If strFind = "" Then
MsgBox "No matching text found!", vbExclamation
Exit Sub
End If
strReplace = InputBox("Enter replacement text")
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "No folder selected!", vbExclamation
Exit Sub
End If
End With
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
Application.ScreenUpdating = False
strFile = Dir(strPath & "*.xls*")
Do While strFile <> ""
Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
For Each wsh In wbk.Worksheets
wsh.Cells.Replace What:=strFind, Replacement:=strReplace, _
LookAt:=xlWhole, MatchCase:=False
Next wsh
wbk.Close SaveChanges:=True
strFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Find & Replace Complete"
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,215,219
Messages
6,123,684
Members
449,116
Latest member
HypnoFant

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
Back
Top