CODEWITHSUNDEEP
excelvba
Tom0101
Tom0101

Reputation: 3

How to add modification to all formulas from a selected range in excel

I would like to limit all of the formulas in a selected range of cells with an additional if statement.

Is it doable with the use of the VBA?

Let's say: cell A1 contains =sum(B1;C1) but it can be any formula that a selected cell contains after applying macro updated formula would be =if(D100>0;sum(B1;C1);0)

=if(D100>0;any formula in a cell ;0)

Upvotes: 0

Views: 152

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

You can achieve this by using code like this:

Sub ModifyFormulas()
    Dim cl As Range, formulas_cells As Range
    Const template = "=if(D100>0,#,0)"  '# will be replaced by old formula without '='
    On Error Resume Next    ' handle an error if there are no formulas in the range
    Set formulas_cells = Range("A1:A10").SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not formulas_cells Is Nothing Then
        For Each cl In formulas_cells
            cl.Formula = Replace(template, "#", Mid(cl.Formula, 2))
        Next
    End If
End Sub

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8375

Without vba, I use the following method:

  1. edit / replace the "=" with "xyxy" - this stops excel recalculating

  2. then edit / replace "sum" with "if(D100>0;sum"

  3. then edit / replace ")" with ");0)"

  4. lastly replace "xyxy" with "="

and then it all works, done this often with my sheets when I have to change some calculations...

Upvotes: 0

Related Questions