Search and Replace Part of Formulas

Alain

Try this on a copy of your workbook - the sheet where you want the formulas changed should be visible in Excel (ie Active) when you run this. It will only amend formulas with

LoB1!XX

in the B column

Sub change_formulas()
Dim r As Range

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(LoB1\!)(($)?[A-Z]{1,2}($)?[0-9]+)"
        For Each r In Application.Intersect(ActiveSheet.Range("B:B"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeFormulas)
                r.FormulaLocal = .Replace(r.FormulaLocal, "IF(scenario=1;'Lob1'!$2;'LoB2'!$2)")
        Next r
End With
        
End Sub

Richard