Replacing a value with another value on multiple files in given folder

Hi,

I have multiple files in one folder and need to replace value <25 with <21 on all the files on column DI

Above replacement to be made on all the files of that given folder.

Quote from KjBox

Is there just one worksheet in each of the files?

Yes

Sheet name is "Data"

There are multiple files in a given folder.

Each file has same sheet name "Data"

In Data sheet at column DI there is formulae containing a value "<25".

What I need is to find the value "<25" in given column DI and replace it by value"<21".

It has to be done for all the files present in that folder.

Quote from KjBox

Try this, change the path to the required folder where shown (make sure you include a backslash at the end)

Code

Sub UpdateAllFiles()
    Dim x, i As Long, fName, wbk As Workbook
    
    fName = Dir("C:\Users\charl\Desktop\Akhas\") 'CHANGE this to the path to your folder
    
    Application.ScreenUpdating = 0
    While fName <> ""
        Set wbk = Workbooks.Open(fName)
        With wbk.Sheets(2)
            x = .UsedRange.Columns(113).Formula
            For i = 1 To UBound(x, 1)
                x(i, 1) = Replace(x(i, 1), "<25", "<21")
            Next
            .UsedRange.Columns(113).Formula = x
            .Parent.Close 1
        End With
        fName = Dir
    Wend

End Sub

Display More

Great KJ,

Its Working Fantastic

Thanks for your great help.