Macro - add formula to a formula

[bakesbrooklyn]

Hi all,

I would like a macro that adds a formula to a cell that already contains a formula. I have a document with a lot of formulas in it, however I now need to round all of these cells up so I need to add =ceiling(ORIGINAL FORMULA, 10) to every formula.

So - if cell contains formula then add the formula around it.

I am using Microsoft Excel 2013.

Thank you!

 

[RoryA]

Hi, and welcome to the forum.

Try this:

Sub RoundFormulas()
    Dim cell As Range
    Application.Screenupdating = False
    For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        cell.Formula = "=CEILING(" & Mid$(cell.Formula, 2) & ",10)"
    Next cell
    Application.Screenupdating = True
End Sub

 

[bakesbrooklyn]

Hi, and welcome to the forum.

Try this:

Sub RoundFormulas()
    Dim cell As Range
    Application.Screenupdating = False
    For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        cell.Formula = "=CEILING(" & Mid$(cell.Formula, 2) & ",10)"
    Next cell
    Application.Screenupdating = True
End Sub

It worked! Thank you so much - I will definitely use this for more things

 

[RoryA]

Glad to help.

 

[bakesbrooklyn]

Glad to help.

Another question and apologies for being so ridiculously inept at VBA! I ran the macro, which worked beautifully. However, afterwards I have decided to round up to 100 instead of 10 - now I have been able to create the macro for doing this but I had already saved the document after running the first macro. Is there a macro that can amend the formula to change the current ceiling formula to show by 100 rather than 10? This I feel could also be useful for mass amending formulas in the future.

Thank you again in advance!

 

[RoryA]

It would be quite complicated to parse all possible formula variations, but for this task you could simply use something like:

Sub RoundFormulas()
    Dim cell As Range
    Application.Screenupdating = False
    For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        if UCase$(left$(cell.Formula, 9)) = "=CEILING" and Right$(cell.Formula, 4) = ",10)" Then
            cell.formula = left$(cell.formula, len(cell.formula) - 4) & ",100)"
        End If
    Next cell
    Application.Screenupdating = True
End Sub

You might also be able to get away with a non-VBA find/replace to find ,10) and replace with ,100)