Hello to anyone.
I'm trying to find a solution (a vba) that open all excel files inside a folder, then search IN ALL WORKSHEETS of each excel file for "40;" and replace it with "48;" , then save and close files.
I found the following vba in microsoft support website, but it doesn't work for me. It doesn't change anything.
The "40;" i want to change is part IF(ISNA(VLOOKUP.....)) formula.
If i open manually the files and use the excel default Find & Replace function it works like a charm. But as you understand it will take me a lot of time to make this change manually by opening one by one the 100+ files i have to change.
P.S. All excel files and their sheets are not protected.
Thanks In Advance for your time.
I'm trying to find a solution (a vba) that open all excel files inside a folder, then search IN ALL WORKSHEETS of each excel file for "40;" and replace it with "48;" , then save and close files.
I found the following vba in microsoft support website, but it doesn't work for me. It doesn't change anything.
Code:
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
strFind = InputBox("Enter text to find")
If strFind = "" Then
MsgBox "No find text specified!", 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
End Sub
The "40;" i want to change is part IF(ISNA(VLOOKUP.....)) formula.
If i open manually the files and use the excel default Find & Replace function it works like a charm. But as you understand it will take me a lot of time to make this change manually by opening one by one the 100+ files i have to change.
P.S. All excel files and their sheets are not protected.
Thanks In Advance for your time.
Last edited: