VBA to change specific formulas to values when file is saved

[aleigh121]

I have a file that points to a separate database. I need to convert all formulas that start with a specific text to values when a user saves the file. Currently the file is a Excel Macro Enabled Template and opens in read only. I would like the file to convert all formulas on all sheets that start with "=E10" to values when it saves so the file doesnt come back with a #NAME error. I don't want to change any of the sum or vlookup formulas and the formulas that start with "=E10" can continue with a multitude of different variances. Is this possible?

 

[Joe4]

Welcome to the Board!

Depending on how you sheet is structured, it might be easier to update the "=E10" formulas based on location.
Where exactly do these formulas reside on your sheet?
Where exactly do the other formulas you don't want updated reside ion your sheet?

 

[aleigh121]

Its a financial report so there will be 2-25 lines in 6 difference columns with the E10 formulas and then a sum formula, repeated about 10 times. This is about 98% of the total report. There are over 5,600 (i had to do a find and replace on one of them at one point) on each sheet (9 total, all identical) and then i have one sheet that just has 2 rows of them with a sum formula every 10 columns

 

[Joe4]

Try this VBA code:

Sub ReplaceFormulaMacro()
    
    Dim ws As Worksheet
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
'   Loop through all sheets
    For Each ws In Worksheets
        ws.Activate
'       Loop through all instances of =E10 formula
        Do
            Set rng = Cells.Find(What:="=E10", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
            If rng Is Nothing Then
'               Exit loop if no more cases found on sheet
                Exit Do
            Else
'               If found, replace with value
                rng.Value = rng.Value
            End If
        Loop
    Next ws
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
                
End Sub