CODEWITHSUNDEEP
excelvba
Lukas
Lukas

Reputation: 3

Excel VBA - replace cells as value for particular formulas

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

Upvotes: 0

Views: 463

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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

Upvotes: 1

Related Questions