VBA replacement not working for specific input-CodePudding

I am trying to make a VBA macro in excel which searches for a heading (succeeded at that), then selects the column of the heading (succeeded at that too) to finally do a replacement through the selected column (only partly succeeded in this).

I want to replace e.g SUM(C22:G22) with SUM(C22:INDIRECT(ADDRESS(ROW(); COLUMN()-1; 4))). It works just fine in excel when I select a column and then ctrl H and find what :*) replace with :INDIRECT(ADDRESS(ROW(); COLUMN()-1; 4))).

Below is the code I tried that works:

Sub FindString_Search_Replace_Column()
    Rows("1:1").Select
    Selection.Find(What:="XTOTAL 2021", After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.Replace What:=":*)", Replacement:=":A3)", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

It does exactly what I want when it "just" needs to replace with e.g. A3. However, when the code replacement bit gets a bit longer, see below code, with what I actually need, it seems like it stops right after ActiveCell.EntireColumn.Select. It never actually changes anything.

Sub FindString_Search_Replace_Column()
    Rows("1:1").Select
    Selection.Find(What:="XTOTAL 2021", After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.Replace What:=":*)", Replacement:=":INDIRECT(ADDRESS(ROW(); COLUMN()-1; 4)))", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

I am thinking it is the inside ADDRESS bit it kind of chokes on but here my google skills cannot help me any further (no results suiting my problem).

Any ideas? :)

CodePudding user response：

Try replacing the ; separators in your formula with , - when adding a formula in VBA typically you use "US-style" separator (unless assigning the formula via FormulaLocal).

Possibly that also applies to updating an existing formula.