Excel VBA - replace cells as value for particular formulas

Question

I have the big Excel with a lot of formulas. I would like to save this Excel as values but only for some formulas not for all.

I use this VBA script - but this replaces all formulas by values. How can I replace for example only all cells which contain formula SUM - maybe which contain =SUM(

Sub Saveasvalue()
    Dim wsh As Worksheet
    For Each wsh In ThisWorkbook.Worksheets
        wsh.Cells.Copy
        wsh.Cells.PasteSpecial xlPasteValues
    Next
    Application.CutCopyMode = False
End Sub

shrivallabha.redij · Accepted Answer · 2021-02-24 13:02:58Z

1

You will have to add a checking condition like below to test if the cell contains SUM formula.

Sub Saveasvalue()
    Dim wsh As Worksheet
    Dim rng As Range
    For Each wsh In ThisWorkbook.Worksheets
        For Each rng In wsh.UsedRange
            If InStr(1, rng.Formula, "SUM(", vbTextCompare) > 0 Then
                rng.Value = rng.Value
            End If
        Next rng
    Next
End Sub

answered Feb 24, 2021 at 13:02

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - replace cells as value for particular formulas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related