update/modify formula VBA

December 27, 2021

I have multiple sheets that should have references to another sheet.
For example cell B3,sheet AC: =Inlife!G4. Now I create two new sheets AC (2) and Inlife (2), the reference for B3,sheet AC (2) should be:
=Inlife (2)!G4

I have tried many variations of the replace option but so far all that I managed to do was remove the formula and leave a value or blank cell.

  Dim rng As Range, cell As Range
    Set rng = Sheets("AC (2)").Range("B3:B10")
    

    For Each cell In rng
  cell = WorksheetFunction.Substitute(cell, "Inlife", "Inlife (2)")

    Next

Does anyone know a way to update all the references/formulas in one go?
(I have tried to just use the search and replace function of excel but that gave me an error about the formula)

>Solution :

Please, try:

    For Each cell In rng
      cell.Formula = Replace(cell.Formula, "Inlife", "Inlife (2)")
    Next

I would also like to suggest replacing of cell variable with cel. Cell is a range property and it is good to avoid creating such variables. It may create confusions on a complex code…