update/modify formula VBA-CodePudding

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)

CodePudding user response：

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...

CodePudding user response：

In the end this worked well.

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

    For Each cel In rng
    cel.Formula = Replace(cel.Formula, "Inlife", "'Inlife (2)'")

    Next