Need to replace a formula while keeping some of the contents in the formula _ VBA!

[luismemo21]

Hello All,

First post on this forum. Just to be clear, I have already searched similar threads (here and and elsewhere) and tried to piece together a VBA code that will work, but I just don't have the adequate experience under my belt to write a executable code.

I have a maybe a thousand cells across several hundred worksheets that contain formulas in the following format:

=(max(G11,H11)-G15+S15+O11)

And I would like to change the formula to:

=G11-G15-S15+O11

The formulas are all along a single column, lets say Column "S".

Using Find>Replace would work if I was uniformly changing the equation across all cells. But each formula is unique and I have to keep the specific references for that formula.

My logic is as follows:

1. For each cell in the chosen range (in this case, a range in column S), check to see if the formula starts with "=(max("
2. If it does, then place the references I need in variables (e.g. var1 = G11, var2 = G15, etc etc). Right now I am using the "Mid" function to extract those references from the old equation.
3. Replace the current formula with a new formula (e.g. =var1 - var2 - var3 + var4)

This is my code so far, but I am still fairly new to VBA so I'm sure there's a lot wrong here

Function CellFormula(Rng As Range) As String
    CellFormula = Rng.Formula
End Function

Sub EnrgyEqFix()

'equation format that I wish to be changed:
'=(max(G11,H11)-G15+S15+O11)
'equation format that I wish to have at the end:
'=G11-G15+S15+O11
'specific cell refrences must be kept for each formula

Dim ds_elv As String     'downstream elevation reference i.e. first "G" reference
Dim us_elv As String     'Upstream elevation reference i.e. second "G" reference
Dim us_energy As String  'upstream cumulative energy loss i.e. "S" refrence
Dim h_loss As String     'friction loss thru pipe i.e. "O" refrence
Dim ERng As Range        'range of cells that contains Energy Formula
Dim Ecell As Range       'cell in ERng
Dim ToRep As String      'condition to see if formula should be replaced
Dim NewEq As String      'new equation I wish to have


Set ERng = Range("S11:S300")
'range of cells that possibly have old equation
ToRep = "=(Max("
'this is the string to look for and decide if a change should be made
NewEq = "=ds_elv-us_elv-us_energy+h_loss"
'this is the end equation I want to have

For Each Ecell In ERng.Cells

    If Left(CellFormula(Ecell), 4) = ToRep Then
        ds_elv = Mid(CellFormula(Ecell), 7, 3)
        us_elv = Mid(CellFormula(Ecell), 16, 3)
        us_energy = Mid(CellFormula(Ecell), 20, 3)
        h_loss = Mid(CellFormula(Ecell), 24, 3)
        
        ActiveCell.Value = NewEq
    End If

Next Ecell

End Sub

Any advice or pointers would be much appreciated. Thank you all!