Find And Replace in Multi excel files with multi worksheets

[kalagas]

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.

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.

 

[Fluff]

Try making this change

        For Each wsh In wbk.Worksheets
            wsh.Cells.Replace What:=strFind, Replacement:=strReplace, _
                LookAt:=[COLOR=#ff0000]xlPart[/COLOR], MatchCase:=False
        Next wsh

 

[kalagas]

Try making this change

        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.

 

[Fluff]

Ok, how about

wsh.Cells.Replace strFind, strReplace, xlPart, , False, , False, False

 

[kalagas]

Ok, how about

wsh.Cells.Replace strFind, strReplace, xlPart, , False, , False, False

Again nothing is changing...

 

[Fluff]

What are the EXACT values you are putting in the 2 input boxes?

 

[kalagas]

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]

And what is the complete formula that you are trying to change?

 

[kalagas]

And what is the complete formula that you are trying to change?

=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]

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.