Change part of an excel forumla to include data from another cell

Question

I am looking for an easy way to replace part of a formula in Excel.

I have to do this for over 66,000 formulas every year. Currently i use Find & Replace to speed up the job but it still takes me several hours to do it.

='C:\Excel\[File1.xlsm]08-01-20'!V5

I want to replace the 08-01-20 part of the above formula with the value of cell C2

Exhausted Exhausted · Accepted Answer · 2019-12-10T04:37:35

Create two lists in the format "dd-mm-yy", (one with the incorrect dates, one with the correct

Then replace the example dates in the below script & Run.

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

 fndList = Array("04-01-20", "11-01-20")
 rplcList = Array("05-01-20", "12-01-20")

  For x = LBound(fndList) To UBound(fndList)
      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
          lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht

  Next x

End Sub

Change part of an excel forumla to include data from another cell

2 Answers