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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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:
Upvote 0
Ok, how about
Code:
wsh.Cells.Replace strFind, strReplace, xlPart, , False, , False, False
 
Upvote 0
What are the EXACT values you are putting in the 2 input boxes?
 
Upvote 0
And what is the complete formula that you are trying to change?
 
Upvote 0
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;
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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