Excel VBA Code to find and replace strings in specified cell range in multiple files
top of page
Search
    • Nov 27, 2017

Excel VBA Code to find and replace strings in specified cell range in multiple files


You can use the below vba code to find and replace multiple sets of strings in a specified range of cells for all Excel files in a particular directory.

In the below example, the code is set to replace all examples of the entries set by the 'What:=' command in the first row of the first worksheet. If you want to only have the find and replace operate on columns A to D in the first row, you would edit:

Worksheets(1).Rows("1").Replace _

. . . to:

Worksheets(1).Range("A1:D1").Replace _

Note that each successive range needs to include these three lines listing the worksheet and range, the find term & replace term, and the settings for the letter case and search order.

Worksheets(1).Rows("1").Replace _ What:="&", Replacement:="AND", _ SearchOrder:=xlByColumns, MatchCase:=True

The directory containing the files with the data to be replaced is set at the beginning of the macro.

Sub LoopThroughFiles() FolderName = "R:\Firm\Scannings\osheas\2017.11.21 A4552.0021 Remainder\part 1 v2\process\xls\" If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator fname = Dir(FolderName & "*.xls") 'loop through the files Do While Len(fname) With Workbooks.Open(FolderName & fname) ' here comes the code for the operations on every file the code finds Worksheets(1).Rows("1").Replace _ What:="&", Replacement:="AND", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets(1).Rows("1").Replace _ What:="-", Replacement:="_", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets(1).Rows("1").Replace _ What:="%", Replacement:="perect", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets(1).Rows("1").Replace _ What:="=", Replacement:="equals", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets(1).Rows("1").Replace _ What:="<", Replacement:="_", _ SearchOrder:=xlByColumns, MatchCase:=True Worksheets(1).Rows("1").Replace _ What:="$", Replacement:="dollar", _ SearchOrder:=xlByColumns, MatchCase:=True ActiveWorkbook.Close SaveChanges:=True End With ' go to the next file in the folder fname = Dir Loop End Sub


bottom of page