Racking my brain trying to replace a substring in a formula without replacing a slightly larger subs-CodePudding

To put it simply, I have a cell with the formula

=IF(A4="Name", IFERROR(J4*(I4-AI4), 0), "")

I am trying to replace "I4" with say "BH4" without "AI4" also being turned into "ABH4". But it cannot be assumed that the formula will always follow this pattern (so not really an option to just find and replace the 1st occurrence of the string or something like that

I've looked into both the Replace and Substitute function but they don't seem to handle wildcards. I also tried using the Range.Replace method which I think is suppose to be able to handle wildcards, but I was unable to really get it to work. But even then if I'm not really sure what wildcard setup would work

CodePudding user response：

Say the formula is in cell B9.

This will only change the first occurrence of the substring:

Sub FixFormula()
    With Range("B9")
        .Formula = Application.Substitute(.Formula, "I4", "BH4", 1)
    End With
End Sub

CodePudding user response：

I think you would need to use a regex something like this:

Sub regex()

    Set myRegExp = New RegExp
    myRegExp.Pattern = "([^a-zA-Z])I4([^a-zA-Z])"
    
    With Range("A1")
        .Formula = myRegExp.Replace(.Formula, "$1AB4$2")
    End With

End Sub

Note that you have to go to Tools|References first in the VBA screen and enable Microsoft VBScript regular expressions 5.5.