Find And Replace in Multi excel files with multi worksheets

kalagas

New Member
Joined
Feb 15, 2019
Messages
25
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.

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:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
Try making this change
Code:
        For Each wsh In wbk.Worksheets
            wsh.Cells.Replace What:=strFind, Replacement:=strReplace, _
                LookAt:=[COLOR=#ff0000]xlPart[/COLOR], MatchCase:=False
        Next wsh
 

kalagas

New Member
Joined
Feb 15, 2019
Messages
25
Try making this change
Code:
        For Each wsh In wbk.Worksheets
            wsh.Cells.Replace What:=strFind, Replacement:=strReplace, _
                LookAt:=[COLOR=#ff0000]xlPart[/COLOR], MatchCase:=False
        Next wsh
I tried it but same result. Nothing changes.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Code:
wsh.Cells.Replace strFind, strReplace, xlPart, , False, , False, False
 

kalagas

New Member
Joined
Feb 15, 2019
Messages
25

ADVERTISEMENT

Ok, how about
Code:
wsh.Cells.Replace strFind, strReplace, xlPart, , False, , False, False
Again nothing is changing...
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
What are the EXACT values you are putting in the 2 input boxes?
 

kalagas

New Member
Joined
Feb 15, 2019
Messages
25

ADVERTISEMENT

What are the EXACT values you are putting in the 2 input boxes?
in the 1st input box i put "40;" and in the 2nd "48;" (without the quotes of course)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
And what is the complete formula that you are trying to change?
 

kalagas

New Member
Joined
Feb 15, 2019
Messages
25
And what is the complete formula that you are trying to change?
Code:
=IF(ISNA(VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!A5:J40;10;FALSE))=TRUE;"0";VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!A5:J40;10;FALSE))+IF(ISNA(VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!L5:U40;10;FALSE))=TRUE;"0";VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!L5:U40;10;FALSE))+IF(ISNA(VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!W5:AF40;10;FALSE))=TRUE;"0";VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!W5:AF40;10;FALSE))+IF(ISNA(VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!AI5:AR40;10;FALSE))=TRUE;"0";VLOOKUP("ΧΧΧΧΧΧ";'C:/RESTAURANTS/E DP-10.06 (WORKING HOURS) METRE/2019/[E DP-10.06 (WORKING HOURS) METRE_11_NOVEMBER_2019.xlsm]21.11.2019'!AI5:AR40;10;FALSE))

And i want to 40; with 48;
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
That works for me.
Try stepping through the code using F8 and when you get to the line that doing the replacing, hover the cursor over strFind & strReplace to check that they still fold the correct values.
Also at the same time you can check that it is actually opening workbooks.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,726
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top